Re: Find overlapping time intervals, how?

On Tue, Sep 09, 2003 at 09:29:58PM +0200, Holger Marzen wrote:
> Say, we have uptimes from several servers:
>
> Server     up_from  up_to
> ------     -------  -------
> s1         0:00     8:00
> s1         10:00    20:00
> s1         22:00    24:00  (would better be a timestamp with 0:00 and next day)
> s2         0:00     4:00
> s2         6:00     23:00

You better get a timestamp field for the up_from and up_to, because it's
going to be very difficult to deal with only times (unless you want
a report of a single day).

I've done a similar thing with something like:

select
    max(s1_uptime.up_from, s2_uptime.up_from) as start,
    min(s1_uptime.up_to, s2_uptime.up_to) as end
from (select up_from, up_to from table where server=s1) as s1_uptime,
     (select up_from, up_to from table where server=s2) as s2_uptime
where (s1_uptime.up_from, s1_uptime.up_to) overlaps
    (s2_uptime.up_from, s2_uptime.up_to)

I'm not sure if the OVERLAPS operator works on released versions; I've
only used it on 7.4, and even there it was undocumented until yesterday
(I found it peeking at the source looking for something else).

You also have to build max(timestamp, timestamp) and min(timestamp,
timestamp) functions:

create function max(timestamp, timestamp) returns timestamp as
'select case if $1 > $2 then $1 else $2' language sql;

(see here for OVERLAPS:
http://developer.postgresql.org/docs/postgres/functions-datetime.html )

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
www.google.com: interfaz de linea de comando para la web.