Calculate time difference in seconds excluding weekends and a time period in PHP

Question

I have given up on trying to do this in MySQL and instead I'm going to inject the result into my array of results (which is used later in a JS app).

I have a loop to go through each result:

$data = $result->fetchAll();

foreach($data as $i => $row) {
    // Something                    
    $data[$i]['Age'] = $row['Age'];
}

I want it to add up the seconds between $data[$i]['Age'] which is a datetime and the current datetime.

Normally this would be easy but how do I exclude weekends AND time between 16:30 and 07:30?

possible duplicate of [PHP - Calculating working hours between two dates but exclude the time when request is on hold](http://stackoverflow.com/questions/20286781/php-calculating-working-hours-between-two-dates-but-exclude-the-time-when-requ) — Jonathon, Aug 18 '14 at 13:11
[Calculate business days](http://stackoverflow.com/questions/336127/calculate-business-days) should be a good starting point — pulsar, Aug 18 '14 at 13:12
You make a standard add, then carefully calculate the duration of discounted timespans during the interval and subtract it from the sum. — Jon, Aug 18 '14 at 13:12
@NoyGabay I'm going to ignore holidays for now until I have a grasp of this. Later on they will be brought in from a holiday table I imagine. — imperium2335, Aug 18 '14 at 13:16
@verbumSapienti That works on days not seconds/minutes like I need. Ultimately, the output displayed to the user is an age column that can show things like "34 seconds", "51 minutes", "6 hours", "3 days" etc etc. — imperium2335, Aug 18 '14 at 13:17
@imperium2335: No. Also, verbumSapienti gave a helpful *starting point*, not a solution. IMO it is extremely unlikely that someone will bother writing a solution to what is a very specific and very uninteresting problem. — Jon, Aug 18 '14 at 13:19

score 0 · Answer 1 · answered Aug 19 '14 at 17:11

I don't think this is too specific or uninteresting, so here is the answer thanks to one of my colleagues:

public static function calculateTime($startDate, $endDate) {//Returns Seconds Passed

        date_default_timezone_set('Europe/London');

        //Opening Hours - Can pull from database depending on users working hours

        $workingHoursOpen = new DateTime('07:30:00');

        $workingHoursClose = new DateTime('16:30:00');



        //Time worked from and to

        $timeStarted = strtotime(date('H:i:s', strtotime($endDate)));

        $timeFinished = strtotime(date('H:i:s', strtotime($startDate)));



        $workingSeconds = $workingHoursClose->getTimestamp() - $workingHoursOpen->getTimestamp();

        $workingSecondsv2 = $timeFinished - $timeStarted;



        //Option to send array of holidays (3rd param)

        $workingDays = Util::getWorkingDays(date('Y-m-d', strtotime($startDate)), date('Y-m-d', strtotime($endDate)), array());



        $totalWorkingSeconds = $workingDays * $workingSeconds; //Working days * 9 hours



        $secondsClosed = 0;

        $i = 0;

        while ($i < $workingDays) {

            $secondsClosed = $secondsClosed - (15 * 3600);

            $i++;

        }



        $secondsPassed = ($workingSecondsv2 - $totalWorkingSeconds) + (9 * 3600);



        $secondsPassed = -1 * ($secondsPassed); // Invert number (was giving -XX)



        return $secondsPassed;

    }



    public static function getWorkingDays($startDate, $endDate, $holidays) {

        // do strtotime calculations just once

        $endDate = strtotime($endDate);

        $startDate = strtotime($startDate);



        $days = ($endDate - $startDate) / 86400 + 1;



        $no_full_weeks = floor($days / 7);

        $no_remaining_days = fmod($days, 7);



        //It will return 1 if it's Monday,.. ,7 for Sunday

        $the_first_day_of_week = date("N", $startDate);

        $the_last_day_of_week = date("N", $endDate);



        //---->The two can be equal in leap years when february has 29 days, the equal sign is added here

        //In the first case the whole interval is within a week, in the second case the interval falls in two weeks.

        if ($the_first_day_of_week <= $the_last_day_of_week) {

            if ($the_first_day_of_week <= 6 && 6 <= $the_last_day_of_week)

                $no_remaining_days--;

            if ($the_first_day_of_week <= 7 && 7 <= $the_last_day_of_week)

                $no_remaining_days--;

        }

        else {

            // (edit by Tokes to fix an edge case where the start day was a Sunday

            // and the end day was NOT a Saturday)

            // the day of the week for start is later than the day of the week for end

            if ($the_first_day_of_week == 7) {

                // if the start date is a Sunday, then we definitely subtract 1 day

                $no_remaining_days--;



                if ($the_last_day_of_week == 6) {

                    // if the end date is a Saturday, then we subtract another day

                    $no_remaining_days--;

                }

            } else {

                // the start date was a Saturday (or earlier), and the end date was (Mon..Fri)

                // so we skip an entire weekend and subtract 2 days

                $no_remaining_days -= 2;

            }

        }



        //The no. of business days is: (number of weeks between the two dates) * (5 working days) + the remainder

//---->february in none leap years gave a remainder of 0 but still calculated weekends between first and last day, this is one way to fix it

        $workingDays = $no_full_weeks * 5;

        if ($no_remaining_days > 0) {

            $workingDays += $no_remaining_days;

        }



        //We subtract the holidays

        foreach ($holidays as $holiday) {

            $time_stamp = strtotime($holiday);

            //If the holiday doesn't fall in weekend

            if ($startDate <= $time_stamp && $time_stamp <= $endDate && date("N", $time_stamp) != 6 && date("N", $time_stamp) != 7)

                $workingDays--;

        }



        return $workingDays;

    }

Works 100% and can be expanded to bring in work hours from a database.

Calculate time difference in seconds excluding weekends and a time period in PHP

1 Answers1