Skip site navigation (1) Skip section navigation (2)

Re: Intersection of two time segments

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Intersection of two time segments
Date: 2005-10-07 18:09:44
Message-ID: 20051007180943.GA2477@kaufbach.delug.de (view raw or flat)
Thread:
Lists: pgsql-novice
Jon Burroughs <Jon(dot)Burroughs(at)noaa(dot)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°

In response to

pgsql-novice by date

Next:From: BluebottleDate: 2005-10-07 23:54:13
Subject: Currval function won't work
Previous:From: Sean DavisDate: 2005-10-07 18:06:40
Subject: Re: Currval function won't work

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group