Calculate working minutes between two Datetime stamps

PHP
1

Hey guys. I posted a question in the MySQL section yesterday regarding a problem that I hoped I could resolve using an SQL query. I have been adviced not to perform this sort of calculation in SQL, but rather do it in PHP instead.

Scenario is as follows: I would like to calculate working minutes between two datetime time stamps in a database. So it is the net amount of minutes between 0800 and 1700 o clock excluding weekends and holidays etc. The timestamps can be only seconds apart or there are multiple days between each of them.

I already found a script on the net which I modified to do what I want. One thing just does not work: It does not exclude the time out of the defined business hours between 8 am and 5 pm.

I have already modified the variables $start_p and $end_p to match the MySQL datetime format and I also changed the “Define ONEDAY” to “Define ONEMINUTE” and it appears to work okay … calculations are performed precisely to the minute, and holidays and weekends are not counted … just the hours between 08:00 and 17:00 are counted as well and I can’t seem to figure out how to make it work.

Here’s the code right now.


<?php // RAY_work_minutes.php
DEFINE ('ONEMINUTE', 60);
 
// ESTABLISH THE MINUTES PER DAY FROM START AND END TIMES
 
$start_time     = '08:00';
$end_time       = '17:00';
 
 
$start_ts = strtotime($start_time);
$end_ts = strtotime($end_time); 
$minutes_per_day = (int)( ($end_ts - $start_ts) / 60 );
 
// ESTABLISH THE HOLIDAYS
$holidays = array
(
        'Jan 19', // MLK Day
        'Jan 20', // Enoggeration Day
        'Feb 16', // Presidents Day
        'Apr 10', // Good Friday
        'May 25'  // Memorial Day
);
 
 
 
// IF WE HAVE FORM DATA
if (!empty($_POST))
{
// CONVERT HOLIDAYS TO ISO DATES
        foreach ($holidays as $x => $holiday)
        {
                $holidays[$x] = date('Y-m-d', strtotime($holiday));
        }
 
// CHECK FOR VALID DATES
        if (!$start = strtotime($_POST["start"])) die('Invalid START Date');
        if (!$end = strtotime($_POST["end"])) die('Invalid END Date');
        $start_p = date('Y-m-d H:i:s', $start);
        $end_p = date('Y-m-d H:i:s', $end);
 
// MAKE AN ARRAY OF DATES
        $workdays = array();
 
// ITERATE OVER THE DAYS
        $start = $start - ONEMINUTE;
        while ($start_p < strtotime($end_time))
        {
        $start = $start + ONEMINUTE;
// ELIMINATE WEEKENDS - SAT AND SUN
                $weekday = date('D', $start);
                if (substr($weekday,0,1) == 'S') continue;
// ELIMINATE HOLIDAYS
                $iso_date = date('Y-m-d', $start);
                if (in_array($iso_date, $holidays)) continue;
                $workdays[] = $iso_date;
// END ITERATOR
        }
 
 
// HOW MANY WORK DAYS AND MINUTES?
        $number_of_workdays = number_format(count($workdays));
        $number_of_minutes = number_format($minutes_per_day * $number_of_workdays);
        echo "<br/>From $start_time to $end_time there are $minutes_per_day Work Minutes";
        echo "<br/>From $start_p through $end_p";
        echo "<br/>$number_of_workdays Work Days";
        echo "<br/>$number_of_minutes Work Minutes";
}
?>
<form action="<?php echo "$PHP_SELF";?>" method="post">
Start Date:<input name="start" /><br/>
End Date:<input name="end" /><br/>
<input type="submit" value="go!" />
</form>

Any help or hint is greatly appreciated.

EDIT typos … syntax …

2

Sorry the script above contains some errors.

Here’s the script that does count the minutes between the two dates correctly:

<?php // RAY_work_minutes.php
DEFINE ('ONEMINUTE', 60);
 
// ESTABLISH THE MINUTES PER DAY FROM START AND END TIMES
 
$start_time     = '08:00';
$end_time       = '17:00';
 
 
$start_ts = strtotime($start_time);
$end_ts = strtotime($end_time); 
$minutes_per_day = (int)( ($end_ts - $start_ts) / 60 );
 
// ESTABLISH THE HOLIDAYS
$holidays = array
(
        'Jan 19', // MLK Day
        'Jan 20', // Enoggeration Day
        'Feb 16', // Presidents Day
        'Apr 10', // Good Friday
        'May 25'  // Memorial Day
);
 
 
 
// IF WE HAVE FORM DATA
if (!empty($_POST))
{
// CONVERT HOLIDAYS TO ISO DATES
        foreach ($holidays as $x => $holiday)
        {
                $holidays[$x] = date('Y-m-d', strtotime($holiday));
        }
 
// CHECK FOR VALID DATES
        if (!$start = strtotime($_POST["start"])) die('Invalid START Date');
        if (!$end = strtotime($_POST["end"])) die('Invalid END Date');
        $start_p = date('Y-m-d H:i:s', $start);
        $end_p = date('Y-m-d H:i:s', $end);
 
// MAKE AN ARRAY OF DATES
        $workdays = array();
 
// ITERATE OVER THE DAYS
        $start = $start - ONEMINUTE;
        while ($start < $end)
        {
        $start = $start + ONEMINUTE;
// ELIMINATE WEEKENDS - SAT AND SUN
                $weekday = date('D', $start);
                if (substr($weekday,0,1) == 'S') continue;
// ELIMINATE HOLIDAYS
                $iso_date = date('Y-m-d', $start);
                if (in_array($iso_date, $holidays)) continue;
                $workminutes[] = $iso_date;
// END ITERATOR
        }
 
 
// HOW MANY WORK DAYS AND MINUTES?
        $number_of_workminutes = number_format(count($workminutes)-1);
        $number_of_minutes = number_format($minutes_per_day);
        echo "<br/>From $start_p through $end_p there are ";
        echo "<br/>$number_of_workminutes Work Minutes";
}
?>
<form action="<?php echo "$PHP_SELF";?>" method="post">
Start Date:<input name="start" /><br/>
End Date:<input name="end" /><br/>
<input type="submit" value="go!" />
</form>
3

I think I figured it out on my own.

Simply added


// ELIMINATE HOURS BEFORE BUSINESS HOURS                
                $daytime = date('H:i', $start);
					 if(($daytime < date('H:i',$start_ts))) continue;
// ELIMINATE HOURS PAST BUSINESS HOURS					 
					 $daytime = date('H:i', $start);
					 if(($daytime > date('H:i',$end_ts))) continue;