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)