Calculating business hours duration, excluding public holidays

Question

I'm trying to determine the duration of a time interval in PHP, taking into account business hours (from 9 am to 5 pm) on Monday to Friday and excluding specific public holidays. Let's consider the public holidays as 15th August 2023, 1st November 2023, 11th November 2023, and 25th December 2023.

For example, suppose I have an interval from 28th October 07:00:00 to 3rd November 10:00:00. How can I calculate the duration (in seconds) only during business hours, excluding weekends and the mentioned holidays?

I'm looking for guidance on an effective approach to achieve this duration calculation.

Answer 1

By using the spatie/opening-hours and briannesbitt/Carbon library, I successfully implemented the desired functionality. The script, utilizing a sample JSON file (sample.json) as input, calculates time differences while excluding public holidays and weekends. The focus is on determining the duration only during working hours.

sample.json

[
{"entity_id":2478013,"end_phase":"2023-10-16T14:39:49.839+02:00","start_phase":"2023-01-02T10:40:01.982+01:00"}
]

Below is the complete code:

<?php
require 'opening-hours-master/vendor/autoload.php';
require 'Carbon-master/vendor/autoload.php';

use Spatie\OpeningHours\OpeningHours;
use Carbon\CarbonInterval;

$openingHours = OpeningHours::create([
    'monday'     => ['09:00-17:00'],
    'tuesday'    => ['09:00-17:00'],
    'wednesday'  => ['09:00-17:00'],
    'thursday'   => ['09:00-17:00'],
    'friday'     => ['09:00-17:00'],
    'saturday'   => [],
    'sunday'     => [],
    'exceptions' => [
        '2023-04-10' => [],
        '2023-05-08' => [],
        '2023-05-18' => [],
        '2023-05-29' => [],
        '2023-08-15' => [],
        '2023-11-01' => [],
        '2023-11-11' => [],
        '07-14'      => [],                // Recurring on each 14 of July
        '05-01'      => [],                // Recurring on each 1st of May
        '01-01'      => [],                // Recurring on each 1st of January
        '12-25'      => []                 // Recurring on each 25th of December
    ],
    'timezone' => [
        'input' => 'Europe/Paris',
        'output' => 'Europe/Paris',
    ]
]);

$result_csv = array(array('entity_id', 'start_phase', 'end_phase', 'difference_in_seconds', 'difference_in_human_readable', 'isBreached'));

$data = file_get_contents('sample.json');
$data = json_decode($data, true);
$isBreached = '';
foreach($data as $row){
    $diffInSeconds = $openingHours->diffInOpenSeconds(new DateTime($row['start_phase']), new DateTime($row['end_phase']));  
    $human_readable = CarbonInterval::seconds($diffInSeconds)->cascade()->forHumans();

    if($diffInSeconds > 144000){
        $isBreached = 'true';
    }else{
        $isBreached = 'false';
    }

    $result_csv[] = array($row['entity_id'], $row['start_phase'], $row['end_phase'], $diffInSeconds, $human_readable, $isBreached);
}

$fp = fopen('file.csv', 'wb');
foreach ($result_csv as $fields) {
    fputcsv($fp, $fields);
}
fclose($fp);