Thread: Check set of date intervals
Hi all,<br /><br />have such relation A:<br /><br />PERIOD_ID | DATE_START | DATE_END | OTHER_ATTRIBUTES...<br /> 1 | 01.01.2010 | 01.02.2010 ....<br /> 2 | 03.02.2010 | 04.03.2010 .....<br />......<br /><br/>I want to search among periods for the set of periods which completely covers passed search period.<br />As example- if I pass search period '15.01.2010 to 15.02.2010' the result set must be empty because there is gap between 01.02and 03.02.<br /><br />please, suggest an idea how to implement this in SQL without writing a procedure.<br /><br />Thanks,<br/>Anton<br />
In response to Anton Gavazuk : > Hi all, > > have such relation A: > > PERIOD_ID | DATE_START | DATE_END | OTHER_ATTRIBUTES... > 1 | 01.01.2010 | 01.02.2010 .... > 2 | 03.02.2010 | 04.03.2010 ..... > ...... > > I want to search among periods for the set of periods which completely covers > passed search period. > As example - if I pass search period '15.01.2010 to 15.02.2010' the result set > must be empty because there is gap between 01.02 and 03.02. > > please, suggest an idea how to implement this in SQL without writing a > procedure. There are a really nice additional contrib module from Jeff Davis, described here: http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/ Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
In response to A. Kretschmer : > > please, suggest an idea how to implement this in SQL without writing a > > procedure. > > There are a really nice additional contrib module from Jeff Davis, > described here: > > http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/ > short example, with your data: test=# select * from rel_a;id | start_date | end_date | p ----+------------+------------+---------------------------------------------------- 1 | 01.01.2010 | 01.02.2010 | [01.01.201000:00:00 CET, 01.02.2010 00:00:00 CET) 2 | 03.02.2010 | 04.03.2010 | [03.02.2010 00:00:00 CET, 04.03.2010 00:00:00CET) (2 rows) test=# select * from rel_a where contains(p, period('15.01.2010'::date, '15.02.2010'::date));id | start_date | end_date |p ----+------------+----------+--- (0 rows) test=# select * from rel_a where contains(p, period('15.01.2010'::date, '25.01.2010'::date));id | start_date | end_date | p ----+------------+------------+---------------------------------------------------- 1 | 01.01.2010 | 01.02.2010 | [01.01.201000:00:00 CET, 01.02.2010 00:00:00 CET) (1 row) The column p is created with: test=# alter table rel_a add column p period; and test=# update rel_a set p = period(start_date, end_date); Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
In response to Anton Gavazuk : > Hi Andreas, > > great thanks for the response, please, answer to the list, not to me, okay? > > unfortunately function just tests every row - it doesnt construct set of > periods which would cover choosed period. That's hard to achieve ... maybe you have to create a table with contiguous periods. Somewhere i have found this code-snippet: test=*# select * from t1;a ---- 1 2 3 4 6 7 810111213 (11 rows) test=*# WITH RECURSIVE RecCols (LeftBoundary, Value) AS (SELECT a, a FROM t1 WHERE (a - 1) NOT IN (SELECT a FROM t1) UNIONALL SELECT p.LeftBoundary, c.a FROM RecCols AS p, t1 AS c WHERE c.a = p.Value + 1) SELECT LeftBoundary, MAX(Value) ASRightBoundary FROM RecCols GROUP BY LeftBoundary ORDER BY LeftBoundary;leftboundary | rightboundary --------------+--------------- 1 | 4 6 | 8 10 | 13 (3 rows) Maybe that's the way you have to go ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99