pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCheck set of date intervals
4+ messages / 2 participants
[nested] [flat]
* Check set of date intervals
@ 2010-05-27 10:07 Anton Gavazuk <antongavazuk@gmail.com>
2010-05-27 10:27 ` Re: Check set of date intervals A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: Anton Gavazuk @ 2010-05-27 10:07 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks,
Anton
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Check set of date intervals
2010-05-27 10:07 Check set of date intervals Anton Gavazuk <antongavazuk@gmail.com>
@ 2010-05-27 10:27 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-05-27 10:43 ` Re: Check set of date intervals A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2010-05-27 10:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Check set of date intervals
2010-05-27 10:07 Check set of date intervals Anton Gavazuk <antongavazuk@gmail.com>
2010-05-27 10:27 ` Re: Check set of date intervals A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2010-05-27 10:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-05-27 12:45 ` Re: Check set of date intervals A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2010-05-27 10:43 UTC (permalink / raw)
To: pgsql-sql
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.2010 00: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:00 CET)
(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.2010 00: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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Check set of date intervals
2010-05-27 10:07 Check set of date intervals Anton Gavazuk <antongavazuk@gmail.com>
2010-05-27 10:27 ` Re: Check set of date intervals A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-05-27 10:43 ` Re: Check set of date intervals A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2010-05-27 12:45 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2010-05-27 12:45 UTC (permalink / raw)
To: pgsql-sql
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
8
10
11
12
13
(11 rows)
test=*# WITH RECURSIVE RecCols (LeftBoundary, Value) AS
(SELECT a, a FROM t1 WHERE (a - 1) NOT IN (SELECT a FROM t1)
UNION ALL SELECT p.LeftBoundary, c.a FROM RecCols AS p, t1 AS c WHERE c.a = p.Value + 1)
SELECT LeftBoundary, MAX(Value) AS RightBoundary 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-05-27 12:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-27 10:07 Check set of date intervals Anton Gavazuk <antongavazuk@gmail.com>
2010-05-27 10:27 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-05-27 10:43 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-05-27 12:45 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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