Finding the sum of time for overlapping values

Question

I have been struggling with this question for a couple of days. I have one machine which can have errors. In a database I have the starting and ending time (in unix time) when the error appeared, and the type of error (number from 5-12). The problem I'm having is that multiple errors can occur at the same time (and overlap).

My table looks like this:

   id| type | from       | to
    1| 6    | 1417179933 | 1417180006
    2| 6    | 1417180035 | 1417180065
    3| 9    | 1417180304 | 1417180409
    4| 6    | 1417180662 | 1417184364
    5| 8    | 1417180662 | 1417186832
    6| 9    | 1417180662 | 1417184364
    7| 12   | 1417180662 | 1417184364
    8| 6    | 1417184364 | 1417186832
    9| 9    | 1417184364 | 1417188054

I need to find the total duration of errors for this machine. I can't sum all differences from the above table since it's possible that two or more errors appeared in the same time interval. Records are sorted in ascending order.

My guess was to compare each record (start and finishing time) with previous and then find the difference in seconds. However, this table can grow over time and it is a problem to search through it.

Is there a clever way in PHP or MySQL to find total time where the machine didn't work, possibly in minutes?

Answer 1

Here's a general approach to summing intervals taking into account potential overlaps, supposing intervals are sorted on their lower value.

2 intervals case

When adding two intervals [a,b] and [c,d], thus (d-c) + (b-a) you count their overlap twice.

• If the overlap is non-zero, then its value is min(b,d) - max(a,c). Since you sorted items on the start of the interval, then 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. You awlays want to remove 0 however.

Thus a general formula is d-c + b-a - max(0,min(b,d)-c)

Generalizing to more intervals

To generalize to more intervals than two, just consider that when you add a new interval [c,d] to any number of previous intervals, you add (d-c) and the overlap that is counted twice is between [c,d] and the union of all previous intervals.

Since you sort intervals on their start values, you only need to consider the last continuous interval of this union, thus for you the last continuous period of downtime.

If [a,b] is your previous last continuous interval and you've just added [c,d]:

• If [a,b] and [c,d] overlap, your last continuous interval becomes [a, max(b,d)] because this 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 because of sorted intervals, the intervals overlap iff c < b

In code

That is probably easier to implement in php than mysql. In pseudo code, assuming each row returns a (start,end) error interval, and [a,b] is your last known continuous interval:

(a,b) = get_first_row();
downtime = b-a;
while( (c,d) = get_next_row() )
{
     downtime += d-c - max(0, min(d,b)-c);
     a = c < b ? a : c;
     b = max(b,d);
}

You can see this code run successfully here : https://3v4l.org/Q2phs

Answer 2

Non-database approach. Probably, it might be modified for databases.

  Start   Finish
  10      13
  12      15
  16      18 

Join both start times and finish times together in one sorted list or array with start flag.

  Time           IsStart
10              Yes
12              Yes
13              No
15              No
16              Yes
....

Make ActiveCounter = 0, walk through the list.
Increment ActiveCounter if IsStart, decrement otherwise
When ActiveCounter becomes > 0, error interval begins,
when ActiveCounter becomes = 0, error interval ends.

Time    ActCnt
10      1       //error state begins
12      2       //it continues
13      1       //still continues
15      0       //the end!  T = 15-10 = 5
16      1       //new error state begins

Answer 3

Stealing an idea from Peterm Calculate total time excluding overlapped time & breaks in MySQL

 SELECT SUM(seconds) total
   FROM
 (
   SELECT MAX(to_date) - MIN(from_date) seconds
     FROM
   (
     SELECT from_date, to_date,
            @g := IF(@e BETWEEN from_date AND to_date OR to_date <= @e, @g, @g + 1) g,
            @e := to_date
       FROM my_table CROSS JOIN 
     (
       SELECT @g := 0, @e := NULL
     ) i
      ORDER BY from_date, to_date
   ) q
    GROUP BY g
 ) q;

Answer 4

Here's MBO's answer turned into php code.

function sumDateTimeDurations(array $datePairs){
        $list = [];
        $numlength = strlen((string)(count($datePairs)*2)); 
        $keyCtr = pow(10 ,$numlength);//Keep the keys unique and sortable
        foreach($datePairs as $pair){
            $list[$pair[0]->getTimestamp() . ($keyCtr++)] = [$pair[0]->getTimestamp(),true]; //true = start
            $list[$pair[1]->getTimestamp() . ($keyCtr++)] = [$pair[1]->getTimestamp(),false]; //false = end
        }
        ksort($list);
        $activeCounter = 0;
        $totalSeconds = 0;
        $lastStart = null;
        foreach($list as $ele){
            $ele[1]?$activeCounter++:$activeCounter--;
            if ($ele[1] && $activeCounter == 1){
                $lastStart = $ele[0];

            }
            if (!$ele[1] && $activeCounter == 0){
                $totalSeconds += ($ele[0]) - $lastStart;
            }
        }
        return $totalSeconds;
    }

Example:

$datePairs[] = [new DateTime("2022-07-18 11:31:22.141183", new DateTimeZone("UTC")),new DateTime("2022-07-18 11:32:22.141183", new DateTimeZone("UTC"))];
$datePairs[] = [new DateTime("2022-07-18 11:31:52.141183", new DateTimeZone("UTC")),new DateTime("2022-07-18 11:32:23.141183", new DateTimeZone("UTC"))];
$seconds = sumDateTimeDurations($datePairs);