Difference between two dates - the midnight hour

: Joel Lipman; Web-Development; 06 April 2011; Hits: 9043

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:

copy raw

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

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:

copy raw

$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

$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:

copy raw

$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

$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

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:

copy raw

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

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

Category: Web-Development :: Article: 339

Difference between two dates - the midnight hour

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support