Adding DateTime intervals with foreach loop

Question

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.

don't know what you are trying to achieve, but clearly `while()` and `foreach()` both not required at the same time, you have to use `while()` only:- https://3v4l.org/RfTmY — Alive to die - Anant, Jun 06 '19 at 08:02

score 1 · Accepted Answer · answered Jun 06 '19 at 07:59

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

I only wish I could up vote this twice ! Thank you for you assitance I had know idea you could do that in the query itself! — Mike Abineri, Jun 06 '19 at 08:01
Glad to be able to help! Most things can be done directly in the query - and it's usually (but not always) better to do it while querying if you can :-) — Qirel, Jun 06 '19 at 08:02

Kaddath · Answer 2 · 2019-06-06T09:08:41.787

The answer from Qirel offers a nice way to do this in SQL, however if you want to understand why your code didn't work:

$base_time = new DateTime('00:00'); does not create an interval, it's a date. It means that if you add 24 hours to it and ask only the time part, it will show '00:00' because you end up the day after.

One solution would have been to declare $base_time as an interval, for example like this: $base_time = new DateInterval('PT0S');

And at the end output directly like this: $base_time->format("%H:%I");

Adding DateTime intervals with foreach loop

2 Answers2