Skip site navigation (1) Skip section navigation (2)

Re: Intersection of two date interval

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Intersection of two date interval
Date: 2007-01-26 20:11:24
Message-ID: 20070126201124.GA11685@wolff.to (view raw or flat)
Thread:
Lists: pgsql-sql
On Fri, Jan 26, 2007 at 12:02:24 +0200,
  Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)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)

In response to

pgsql-sql by date

Next:From: Marcin StępnickiDate: 2007-01-26 22:17:58
Subject: Re: Function returning SETOF using plpythonu
Previous:From: Luís SousaDate: 2007-01-26 17:24:52
Subject: Function returning SETOF using plpythonu

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group