Re: Find overlapping time intervals, how?

holger@marzen.de (Holger Marzen) writes:
> 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
>
>
> Now we want to generate a report of the availability. But for a specific
> application both servers must be up at the same time. So the combined
> uptime would be from 0:00 to 4:00, from 6:00 to 8:00, from 10:00 to
> 20:00 and from 22:00 to 23:00. Then we can calculate an uptime
> percentage.
>
> (Another setup would be if the application is clustered, then the
> uptimes would be ORed instead of ANDed)
>
> What would be the most elegant way to do this? I started with a self
> join and 4 unions covering the 4 cases for start- end endtime:

Have you considered the OVERLAPS predicate?

cctld=# create table server_on (
cctld(# name character varying,
cctld(# up_from timestamptz,
cctld(# up_to timestamptz);
CREATE TABLE
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 00:00', '2003-08-01 08:00');
INSERT 38680519 1
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 10:00', '2003-08-01 20:00');
INSERT 38680520 1
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 22:00', '2003-08-01 24:00');
ERROR:  Bad timestamp external representation '2003-08-01 24:00'
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 22:00', '2003-08-02 00:00');
INSERT 38680521 1
cctld=# insert into server_on (name, up_from, up_to) values ('s2', '2003-08-01 00:00', '2003-08-01 04:00');
INSERT 38680522 1
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 06:00', '2003-08-01 23:00');
INSERT 38680523 1
cctld=# select * from server_on a, server_on b where (a.up_from, a.up_to) overlaps (b.up_from, b.up_to);
 name |        up_from         |         up_to          | name |        up_from         |         up_to
------+------------------------+------------------------+------+------------------------+------------------------
 s1   | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04 | s1   | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04
 s1   | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04 | s2   | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04
 s1   | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04 | s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
 s1   | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04 | s1   | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04
 s1   | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04 | s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
 s1   | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04 | s1   | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04
 s1   | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04 | s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
 s2   | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04 | s1   | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04
 s2   | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04 | s2   | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04
 s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1   | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04
 s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1   | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04
 s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1   | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04
 s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1   | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
(13 rows)

Based on the overlaps, you can generate the period during which the
server was up during the day.  I think Celko had an exercise like this
in _SQL For Smarties_.

At any rate, that's not QUITE up to being the total answer, but I
think you'll find "overlaps" will help make some complex joins go
away.
--
select 'cbbrowne' || '@' || 'libertyrms.info';
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)