Обсуждение: Clever way to check overlapping time intervals ?
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.
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°
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&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° > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- ------------ pasman
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)