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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings