pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedClever way to check overlapping time intervals ?
4+ messages / 4 participants
[nested] [flat]
* Clever way to check overlapping time intervals ?
@ 2011-09-15 23:40 Andreas <maps.on@gmx.net>
2011-09-16 00:15 ` Re: Clever way to check overlapping time intervals ? Andreas Kretschmer <akretschmer@spamfence.net>
2011-09-16 11:15 ` Re: Clever way to check overlapping time intervals ? Frank Bax <fbax@sympatico.ca>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas @ 2011-09-15 23:40 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clever way to check overlapping time intervals ?
2011-09-15 23:40 Clever way to check overlapping time intervals ? Andreas <maps.on@gmx.net>
@ 2011-09-16 00:15 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-09-16 09:17 ` Re: Clever way to check overlapping time intervals ? =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2011-09-16 00:15 UTC (permalink / raw)
To: pgsql-sql
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&a...
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°
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clever way to check overlapping time intervals ?
2011-09-15 23:40 Clever way to check overlapping time intervals ? Andreas <maps.on@gmx.net>
2011-09-16 00:15 ` Re: Clever way to check overlapping time intervals ? Andreas Kretschmer <akretschmer@spamfence.net>
@ 2011-09-16 09:17 ` =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: pasman pasmański @ 2011-09-16 09:17 UTC (permalink / raw)
To: pgsql-sql
You can convert dates to cube, they have overlap operator &&
2011/9/16, Andreas Kretschmer <akretschmer@spamfence.net>:
> 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&a...
> 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°
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
------------
pasman
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clever way to check overlapping time intervals ?
2011-09-15 23:40 Clever way to check overlapping time intervals ? Andreas <maps.on@gmx.net>
@ 2011-09-16 11:15 ` Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Frank Bax @ 2011-09-16 11:15 UTC (permalink / raw)
To: pgsql-sql
On 09/15/11 19:40, Andreas 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.
>
self join with "OVERLAPS" operator:
select t1.*,t2.* from (select * from mytab) as t1
full join (select * from mytab) as t2
where (t1.d1,t1.d2) overlaps (t2.d1,t2.d2)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-09-16 11:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-15 23:40 Clever way to check overlapping time intervals ? Andreas <maps.on@gmx.net>
2011-09-16 00:15 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-09-16 09:17 ` =?ISO-8859-2?Q?pasman_pasma=F1ski?= <pasman.p@gmail.com>
2011-09-16 11:15 ` Frank Bax <fbax@sympatico.ca>
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