Re: Find overlapping time intervals, how?

On Tuesday 09 September 2003 02:29 pm, 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
>
>
> 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:
>
> - Interval of s1 starts earlier and ends earlier than interval of s2
> (up_from of s1 <= up_from of s2 and
> up_to of s1 <= up_to of s2 and
> up_to of s1 > up_from of s2) -- overlap condition
> Then the uptime interval is [up_from of s2, up_to of s1]
> #####
> #####
>
> - Interval of s2 starts earlier and ends earlier than interval of s1
> Vice versa.
> #####
> #####
>
> - Interval of s1 contains interval of s2
> (up_from of s1 <= up_from of s2 and
> up_to of s1 >= up_to of s2)
> Then the uptime interval is [up_from of s1, up_to of s1]
> ########
> ####
>
> - Interval of s2 contains interval of s1
> Vice versa.
> ####
> ########
>
> But this looks ugly.
>

I haven't tested this; but what if you:

1. Use timestamps instead of times to account for crossing over midnight.

2. Using aliases t1 and t2 for the source data table:

SELECT case when t1.up_from >= t2.up_from then t1.up_from as start_time else
t2.up_from as start_time,

case when t1.up_to <= t2.up_to then t1.up_to as end_time else t2.up_to as
end_time,

end_time - start_time as avail_time

INTO temp table temp_avail

FROM tablename as t1, tablename as t2

WHERE t1.server <> t2.server and
(t2.upfrom between t1.up_from and t1_up_to OR
t2.up_to between t1.up_from and t1_up_to) and
t1.server = 's1';

3. Now you can run a query on temp_avail for stats.

???

Andrew Gould