From: Bruno Wolff III <bruno@wolff.to>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Intersection of two date interval
Date: Fri, 26 Jan 2007 14:11:24 -0600
Message-ID: <20070126201124.GA11685@wolff.to> (raw)
In-Reply-To: <200701261202.26168.achill@matrix.gatewaynet.com>
References: <49a67b230701260139n1153ee82x8ae91875118f3cd2@mail.gmail.com>
<20070126095007.GD15591@a-kretschmer.de>
<200701261202.26168.achill@matrix.gatewaynet.com>
On Fri, Jan 26, 2007 at 12:02:24 +0200,
Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> Suha,
> the function is the number of days in the
> maximum of the two start dates , untill , minimum of the two end dates
> interval.
> But in postgresql (7.4.15 at least) there is no MIN(date,date),MAX(date,date)
> functions. So someone has to write them, so you cant avoid some logic there.
> Whats your problem with "CASE" statements?
> what you are basically looking for is smth like the above implementation
> from Andreas.
Postgres (at least since 8.1) has GREATEST and LEAST and they provide the
needed functions.
And you should be able to subtract two dates (not timestamps) to get an
integer. (And to count the number of days rather than the time between, you
need to add 1.)
You also will want to put a floor on the overlap so that you don't get
negative values.
So in your case the query would look something like:
bruno=> SELECT GREATEST(0, LEAST('2007-01-20'::date, '2007-01-21'::date) -
bruno(> GREATEST('2007-01-10'::date, '2007-01-13'::date) + 1);
greatest
----------
8
(1 row)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, achill@matrix.gatewaynet.com
Subject: Re: Intersection of two date interval
In-Reply-To: <20070126201124.GA11685@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox