如何使用PHP查找特定时间段的总工作时间

Question

I have a simple table query like bellow:

SELECT uid, dates, time_in, time_out,
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(time_out)-TIME_TO_SEC(time_in))),'%H:%i') as 
total_wh FROM tb_attendance WHERE uid=10012 AND dates BETWEEN '2017-02-01' AND
'2017-02-07' GROUP BY dates

That will output table like bellow:

uid     dates       time_in     time_out    work_hours
10012   2017-02-01  07:07:02    15:56:48    08:49
10012   2017-02-02  07:13:13    07:13:13    00:00
10012   2017-02-03  06:55:35    06:55:35    00:00
10012   2017-02-06  06:45:50    06:45:50    00:00
10012   2017-02-07  06:53:19    16:06:36    09:13
                 Grand Total                ?????

I need to find grand total for work hours in question marks using PHP. So far I've been trying these codes but it returns incorrect value.

`echo"<table><tr><td>uid</td><td>dates</td><td>time_in</td><td>time_out</td><td>work_hours</td></tr>";

$totalwh=0;
while($row=mysql_fetch_array($sql)){
echo"<tr><td>".$row['uid']."</td>";
echo"<td>".$row['dates']."</td>";
echo"<td>".$row['time_in']."</td>";
echo"<td>".$row['time_out']."</td>";
echo"<td>".$row['total_wh']."</td>";
echo"</tr>";

$workhours=explode(":",$row['total_wh']);

$totalwh+=$workhours[0]*60;
$totalwh+=$workhours[1];
$hours = $totalwh / 60;
$minutes = $totalwh % 60;

}

$x=date('H:i',strtotime("$hours:$minutes"));

echo"<tr><td colspan=4 align='center'>Grand Total </td><td>".$x."</td></tr>
</table>";
`

Please help!

Answer 1

I would recommend calculating the total time in seconds, then formatting the seconds into a date representation string. This formatting can be done with the functon gmdate()

http://php.net/manual/en/function.gmdate.php

The time in seconds could either be fetched directly from the database using one of the query functions or the following could be done.

<?php

//Store total time in seconds
$totalTimeSeconds = 0;

//Loop over rows
while($row=mysql_fetch_array($sql)){
    //Output row

    //Split total working hours into hours and minutes
    $time = explode(":", $row['total_wh']);
    $timeHours = $time[0];
    $timeMinutes = $time[1];

    //Convert and add hours/minutes to total time 
    $totalTimeSeconds += ($timeHours * 60 * 60);
    $totalTimeSeconds += ($timeMinutes * 60);
}

//Output formatted total time in seconds
gmdate("H:i:s", $totalTimeSeconds);

Hope this helps!