Now bear in mind the below is in European date format
Consider the following:
copyraw
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:
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
copyraw
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)? $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
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
- $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:
copyraw
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