Difference between two dates - the midnight hour

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:
copyraw
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
  1.  Date       Customer        Time From       Time To          Hours 
  2.  ---------- --------------- --------------- ---------------- -------------- 
  3.  04/03/2011 Tweedle Dee     10:00           12:00            2.00 
  4.  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:
copyraw
$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
  1.  $thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession']))
  2.   
  3.  $this_time_from1 = date("H:i", strtotime($sub_row['TimeFromSession']))
  4.  $this_time_to1 = date("H:i", strtotime($sub_row['TimeToSession']))
  5.   
  6.  $this_time_from_sql=$thisDateSQL." ".$this_time_from1.":00"
  7.  $this_time_to_sql=$thisDateSQL." ".$this_time_to1.":00"
  8.   
  9.  $sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2)
  10.   
  11.  // Using the examples above this is doing the following: 
  12.  2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00 
  13.  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:
copyraw
$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
  1.  $thisDateSQL=date("Y-m-d", strtotime($sub_row['DateSession']))
  2.   
  3.  $this_time_from1 = date("H:i", strtotime($sub_row['TimeFromSession']))
  4.  $this_time_to1 = date("H:i", strtotime($sub_row['TimeToSession']))
  5.   
  6.  $this_time_from_sql=$thisDateSQL." ".$this_time_from1.":00"
  7.  $this_time_to_sql=$thisDateSQL." ".$this_time_to1.":00"
  8.   
  9.  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:
copyraw
SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated))) AS 'Duration'
  1.  SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated))) AS 'Duration' 
Category: Web-Development :: Article: 339

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.