Return the average time difference between dates excluding “Non-Working hours”

I am making a ticketing system for my company. In my database I record the timestamp of when a ticket is first raised and a timestamp of when the ticket is marked as completed.

I have written a function which returns the average time (hrs) a ticket takes to complete:

public function calculateAvgResolveTime()
{
    $timeQuery = $this->database->query('SELECT ticketCreated, ticketCompletedOn FROM employeeTickets');
    $cumulativeTicketTime = $cumulativeTimes = 0;
    while($time = $timeQuery->fetch_assoc()) {
        $timeCreated = strtotime($time['ticketCreated']);
        $timeCompleted = strtotime($time['ticketCompletedOn']);
        if($timeCompleted > $timeCreated) {
            $cumulativeTimes++;
            $cumulativeTicketTime = $cumulativeTicketTime + ($timeCompleted - $timeCreated);
        }
    }
    $time = ($cumulativeTicketTime / 60 / 60);
    $time = sprintf('%02d:%02d', (int) $time, round(fmod($time, 1) * 60));
    return $time;
}

Is there a way I could exclude certain hours? For example our office is open from 09:00-17:00 Monday to Friday.

At the moment if a ticket is raised at 16:30 on a Friday and is completed 09:15 on Monday the average time would be quite high when actually the ticket only took 45 minutes of working time.

Result of var_export():

array(
    array ( 'ticketCreated' => '2020-02-03 15:59:30','ticketCompletedOn' => '2020-02-04 09:53:35'),
    array ( 'ticketCreated' => '2020-02-04 14:00:00', 'ticketCompletedOn' => '2020-02-04 14:36:00')
)

Answer

You will have to loop over the dates between ticketCreated and ticketCompletedOn day by day. There seems to be no mathy way(or at least not in readable format) to solve this as you have time constraints of excluding Saturdays and Sundays as well as the working period being from 09:00:00 to 17:00:00.

Snippet:

<?php

$data =array(
            array ( 'ticketCreated' => '2020-02-03 15:59:30','ticketCompletedOn' => '2020-02-04 09:53:35'),
            array ( 'ticketCreated' => '2020-02-04 14:00:00', 'ticketCompletedOn' => '2020-02-04 14:36:00')
        );

$sum_time = 0;

foreach($data as $details){
    $start_time = new DateTime($details['ticketCreated']);
    $end_time = new DateTime($details['ticketCompletedOn']);
    $end_of_day = new DateTime($start_time->format('Y-m-d') . ' 17:00:00'); // since a day ends at 17:00

    do{
        $diff  = $end_time->diff($start_time);
        $diff2 = $end_of_day->diff($start_time); 
        if($end_time->format('Y-m-d') === $start_time->format('Y-m-d')){ // meaning finished on the same day
            $sum_time += ($diff->h * 60) + ($diff->i) + ($diff->s / 60);
        }else if(!in_array($end_of_day->format('N'),[6,7])){ // skipping Saturdays and Sundays
            $sum_time += ($diff2->h * 60) + ($diff2->i) + ($diff2->s / 60); // add the day's offset(480 minutes)
        }       

        $end_of_day->add(new DateInterval('P1D'));
        $start_time = new DateTime($end_of_day->format('Y-m-d') . ' 09:00:00'); // start time for next day which is 09:00

    }while($start_time <= $end_time);
}


$avg = $sum_time / count($data);

echo "$avg minutes",PHP_EOL;

Demo: https://3v4l.org/gpFt4

Explanation:

• We first create DateTime instances of the dates.
• We now have a do while loop inside.
• If the end time and start time fall on the same day, we just take differences in terms of hours, minutes and seconds.
• If the end time and start time doesn’t fall on the same day, then we subtract the times from start_time from end_of_day which will be 480 minutes for a proper start or remaining offset of that day till 17:00:00.
• If we come across a day which is Saturday or Sunday, we just skip it.
• In the end, we just print the average by dividing sum by total number of tickets.