I might already have something similar to this but this deserves its own article. Why? Well try to search the web for a PHP/MySQL solution which suggests on how to count the hours between two times on the same date, when one of the hours is on the other side of the midnight hour...

Now bear in mind the below is in European date format

Consider the following:

Date       Customer        Time From       Time To          Hours
---------- --------------- --------------- ---------------- --------------
04/03/2011 Tweedle Dee     10:00           12:00            2.00
08/03/2011 Tweedle Dum     23:30           00:30            -23.00            

Note the last row is obviously incorrect. This is because the script is not changing the date at the stroke of midnight. Take the last row as an example, the equation that's happening is:

$thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession']));

$this_time_from1 = date("H:i", strtotime($sub_row['TimeFromSession']));
$this_time_to1 = date("H:i", strtotime($sub_row['TimeToSession']));

$this_time_from_sql=$thisDateSQL." ".$this_time_from1.":00";
$this_time_to_sql=$thisDateSQL." ".$this_time_to1.":00";

$sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2);

// Using the examples above this is doing the following:
2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00
2011-03-08 00:30:00 - 2011-03-08 23:30:00 = -23.00

This is great for everything during that date as long as the "To Date" never goes past midnight into the next day... But what system doesn't do this (no Microsoft jokes please)?

The Solution
Eureka! Or duh! for those that it's obvious to... Compare the two timestamps, if the "To date" is less than the "From date" then add 1 day to the date:

$thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession']));

$this_time_from1 = date("H:i", strtotime($sub_row['TimeFromSession']));
$this_time_to1 = date("H:i", strtotime($sub_row['TimeToSession']));

$this_time_from_sql=$thisDateSQL." ".$this_time_from1.":00";
$this_time_to_sql=$thisDateSQL." ".$this_time_to1.":00";

if ($this_time_to1<$this_time_from1) {
	$nextDateSQL = date("Y-m-d", strtotime(date("Y-m-d", strtotime($thisDateSQL)) . " +1 day"));
	$this_time_to_sql = $nextDateSQL." ".$this_time_to1.":00";
}

$sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2);

// Using the examples above this is doing the following:
2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00
2011-03-09 00:30:00 - 2011-03-08 23:30:00 = 1.00

Update 2011
Note that if you are using PHP to get data out from a MySQL database and you need to record the duration, you're better off with getting MySQL to calculate the duration:

SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated))) AS 'Duration'

< Prev		Next >