Calculate time between overlapping date ranges

Question

I've a case where i need to figure a formula to calculate total time between date range sets (from->to) that can overlap each other. This is going to be used in a ticketing system where i need to calculate the total open time of all tickets (as part of an SLA agreement) without double calculating time that has been already counted. For example, in the records below:

TicketID	Open Date	Close Date
Ticket 1	'2023-01-02 09:00:00'	'2023-01-02 14:00:00'
Ticket 2	'2023-01-02 11:00:00'	'2023-01-02 15:00:00'
Ticket 3	'2023-01-14 10:00:00'	'2023-01-14 11:00:00'

the total time i would need to have is: from '2023-01-02 09:00:00' to '2023-01-02 15:00:00' and from '2023-01-14 10:00:00' to '2023-01-02 11:00:00', thus a total of 7 hours.

An ideas on where to start?

I've search for similar questions, such as this one PHP Determine when multiple(n) datetime ranges overlap each other but it is somewhat different than the one i need to have.

Please explain in detail how you calculate the 7 hours. Why is the open date greater than the close date for ticket 3? — jspit, Jan 18 '23 at 08:36
@jspit For the first one of 6 hours, overlapping intervals are merged. For the second case, ticket might have been re-opened after marking it as closed. — nice_dev, Jan 18 '23 at 08:39
For me, this would only be logically understandable with a closing date of '2023-01-14 11:00:00' for ticket 3, not 2023-01-02 11:00:00. — jspit, Jan 18 '23 at 08:52
The need is to calculate the total time a system is not available(thus the open tickets), so time of overlapping tickets should not be calculated twice. This means that we have 6 hours for the 2 first tickets and 1 hour for the 3rd one. I edited the Close Date of the 3rd ticket as @jspit noticed that it was not correct! — Stathis, Jan 18 '23 at 16:02
Questions that ask "where do I start?" are typically too broad and are not a good fit for this site. People have their own method for approaching the problem and because of this there cannot be a _correct_ answer. Give a good read over [Where to Start](//softwareengineering.meta.stackexchange.com/a/6367) and [edit] your post. — Jim G., Jan 18 '23 at 18:06

score 1 · Accepted Answer · answered Jan 18 '23 at 22:56

The following solution first compresses all overlapping intervals into one using the reduceOverlap function. This means that intervals are then available which do not overlap. These are then added using the diffTotal function. As a result, a DateInterval object is available that can be formatted as you wish with the Format method.

<?php

function reduceOverlap(array $arr){
  $flag = true;
  while($flag){ 
    $flag = false; 
    foreach($arr as $key1 => $row1){
        foreach($arr as $key2 => $row2){ 
            if($key1 === $key2) continue;
            if(($row1['open'] >= $row2['open'] && $row1['open'] <= $row2['close']) OR
                ($row1['close'] >= $row2['open'] && $row1['close'] <= $row2['close'])){
                $arr[$key1]['open'] = min($row1['open'],$row2['open']);
                $arr[$key1]['close'] = max($row1['close'],$row2['close']);
                unset($arr[$key2]);
                $flag = true;
                break 2;
            }
        }
    }
  }
  return $arr;    
}

function diffTotal(array $arr){
    $date = date_create('1970-01-01 00:00');
    foreach($arr as $row){
        $diff = date_create($row['open'])->diff(date_create($row['close']));
        $date->add($diff);
    }
    return date_create('1970-01-01 00:00')->diff($date);    
}

$times = [
    ['open' => '2023-01-02 09:00:00', 'close' => '2023-01-02 14:00:00'],
    ['open' => '2023-01-02 11:00:00', 'close' => '2023-01-02 15:00:00'],

    ['open' => '2023-01-14 10:00:00', 'close' => '2023-01-14 11:00:00'],
];

$arr = reduceOverlap($times);

$diffTotal =  diffTotal($arr);

var_dump($diffTotal);

Output:

object(DateInterval)#2 (10) {
  ["y"]=>
  int(0)
  ["m"]=>
  int(0)
  ["d"]=>
  int(0)
  ["h"]=>
  int(7)
  ["i"]=>
  int(0)
  ["s"]=>
  int(0)
  ["f"]=>
  float(0)
  ["invert"]=>
  int(0)
  ["days"]=>
  int(0)
  ["from_string"]=>
  bool(false)
}

try self: https://3v4l.org/MPNPC

wow, pretty neat implementation! I did a quick test with several key values and it seems to work as expected, thanks! Would you care to explain further the reduceOverlap function? — Stathis, Jan 19 '23 at 10:47

Calculate time between overlapping date ranges

1 Answers1