I asked a question yesterday in adding together datetime intervals and was pointed to this thread – How we can add two date intervals in PHP
This is great and makes sense. However, when I try to do what the submitted answer says in a foreach
loop, I’m ending up with an incorrect result.
This is a function I have made that gets all the clock in times and out times of staff, they are stored in the db and are created using PHP’s date(H:i:s).
My function gets all the in and out times of any given employee, and my reporting feature I’m working on needs to display the total amount of hours they have worked.
I tried to achieve this by converting the times to datetime objects and using ->diff to get the intervals and thus calculating that days hours, I am then trying use a foreach
loop to add the intervals together thus giving me a sum total of the hours worked in any given date range.
The whole function together is this:
function getTotalHours($staff_id,$from_date,$to_date){ $sql = "SELECT * FROM oc_staff_times WHERE date BETWEEN '$from_date' AND '$to_date' AND staff_id = '$staff_id'"; $result = $this->conn->query($sql); if ($result->num_rows > 0) { while ($row = mysqli_fetch_assoc($result)) { $data[] = $row; } $base_time = new DateTime('00:00'); $total = new DateTime('00:00'); foreach ($data as $time) { $in = new DateTime($time['clock_in_time']); $out = new DateTime($time['clock_out_time']); $interval = $in->diff($out); $base_time->add($interval); } return $total->diff($base_time)->format("%H:%I"); } }
I was hoping to get a monthly total, however it seems I’m only getting one days total as my final result. Here is a screen shot of the UI (the total hours are crudely circled) this also shows the time stamps my function should be adding together.
Advertisement
Answer
You can do this in a single query instead. Use TIMEDIFF()
to get the difference for each row, then convert those to seconds by using TIME_TO_SEC()
, SUM()
those up and put it back into time-format with SEC_TO_TIME()
– all in MySQL!
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`clock_out_time`, `clock_in_time`)))) FROM `oc_staff_times` WHERE `staff_id` = ? AND `date` BETWEEN ? AND ?
Making your function with a prepared statement..
function getTotalHours($staff_id, $from_date, $to_date){ $sql = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`clock_out_time`, `clock_in_time`)))) FROM `oc_staff_times` WHERE `staff_id` = ? AND `date` BETWEEN ? AND ?"; $stmt = $this->conn->prepare($sql); $stmt->bind_param("sss", $staff_id, $from_date, $to_date); $stmt->execute(); $stmt->bind_result($totalTime); $stmt->fetch(); $stmt->close(); return $totalTime; }
- SQL fiddle showing the query in action http://sqlfiddle.com/#!9/525b83/7