Jon Burroughs <Jon.Burroughs@noaa.gov> schrieb:
> I'm doing some temporal queries in PostgreSQL and am in need of a way to
> find the start/end times for overlapping time segments.
>
> I know about this query, which will tell me whether or not a time segment
> overlaps:
> select (timestamp '2001-02-01 0:00', timestamp '2001-09-30 0:00') overlaps
> (timestamp '2001-01-01 0:00', timestamp '2001-04-30 0:00');
>
> But I would like to know the actual start/end times for the overlapping
> segment (i.e. 2002-02-01 to 2001-04-30). Is there something already
> available that will do that, or do I have to write a function to do this?
I have writte a little function, you can see this under:
http://a-kretschmer.de/tools/time_intersect.sql
I create a own type and can test with your example:
test=> select * from timer_intersect (timestamp '2001-02-01 0:00',
timestamp '2001-09-30 0:00', timestamp '2001-01-01 0:00', timestamp
'2001-04-30 0:00');
t1 | t2
------------------------+------------------------
2001-02-01 00:00:00+01 | 2001-04-30 00:00:00+02
(1 Zeile)
(I assume a type in your result: 2002-02-01 are wrong)
Attention: this function is not testet! Feedback are welcome!
Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°