Adding DateTime intervals with foreach loop

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");       
    }
}

JavaScript
​x
 
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.

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 ?

JavaScript
 
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;   
}

JavaScript
 
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

Advertisement

Answer