Here, the general approach to summing intervals, taking into account potential overlaps involving intervals, is sorted by their lower value.
2 time intervals
When you add two intervals [a,b] and [c,d] , thus (dc) + (ba) you count their overlap twice.
If the overlap is nonzero, then its value is min(b,d) - max(a,c) . Since you sorted the items at the beginning of the interval, you know that max(a,c) == c .
If the overlap is 0 , a <= b <= c <= d , so min(b,d) == b , max(a,c) == c and min(b,d) - max(a,c) == b - c <= 0 . However, you want to remove 0 .
Thus, the general formula dc + ba - max(0,min(b,d)-c)
Generalization to more intervals
To generalize to more intervals than two, just think that when you add a new interval [c,d] to any number of previous intervals, you add (dc) , and a match that counts twice is between [c,d] and combining all the previous intervals.
Since you sort the intervals by their starting values, you only need to consider the last continuous interval of this union, so for you the last continuous period of inactivity.
If [a,b] is your previous last continuous interval, and you just added [c,d] :
- If
[a,b] and [c,d] overlap , your last continuous interval becomes [a, max(b,d)] because it is the union of [a,b] and [c,d] - If
[a,b] and [c,d] do not overlap , your last continuous interval becomes [c, d] (NB: we have max(b,d) == b )
Since a < c due to sorted intervals, the intervals overlap iff c < b
In code
This is probably easier to implement in php than in mysql. In pseudocode, assuming that each line returns the error interval (start, end), and [a,b] is your last known continuous interval:
(a,b) = get_first_row(); downtime = ba; while( (c,d) = get_next_row() ) { downtime += dc - max(0, min(d,b)-c); a = c < b ? a : c; b = max(b,d); }
You can see how this code works successfully here: https://3v4l.org/Q2phs