Date: Fri, 16 Sep 2011 02:15:47 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Clever way to check overlapping time intervals ?
Message-ID: <20110916001547.GA32000@tux>
References: <4E728CD7.9040505@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <4E728CD7.9040505@gmx.net>
User-Agent: Mutt/1.5.18 (2008-05-17)

Andreas <maps.on@gmx.net> wrote:

> Hi,
> is there a clever way to check overlapping time intervals ?
> An option named n should be taken from date y to y.
> The same name is ok for another interval.
>
> e.g. table :  mytab ( d1 date, d2 date, n text, v text )
>
> There should be a constraint to provide no row can have a d1 or d2  
> within the interval of another row in case they have the same n.
>
> And no row can have an interval that encloses an existing interval.

You can use the PERIOD data type:
http://temporal.projects.postgresql.org/reference.html

and the new (since 9.0) exclusion constraint:
http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/
http://www.google.de/search?hl=de&q=postgresql+exclusion+constraint&aq=0L&aqi=g-L1&aql=&oq=postgresql+exclusion+
http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°