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.
Any ideas? I thought of area functions when using rectangles instead of
times, but I don't know if that's good.
--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1