将时间戳总和显示为00:01:26,而不是126


Display sum of timestamps as 00:01:26 instead of 126

我在一个网站上工作,在那里工人的工作时间是根据他们什么时候打卡(开始)和什么时候打卡(结束)来计算的。End - start = total。我已经成功计算出了起点和终点之间的差值。将start、end和total插入到DB中。"Shift total"来自数据库中total的SQL SUM。最后一个foreach语句就是所讨论的代码。我想将"Shift total"的格式设置为00:00:00,这样它就会显示为00:01:26而不是126。我试过了。"$row['SUM(total)'] + '00:00:00'。但这并没有奏效。对我该怎么做有什么建议吗?提前感谢。我先粘贴结果,然后再粘贴代码。

____start ____________ 结束 ____________

10:44:46 AM     10:44:49 AM     00:00:03
10:50:12 AM     10:50:14 AM     00:00:02
10:50:27 AM     10:50:32 AM     00:00:05
11:12:02 AM     11:13:18 AM     00:01:16
                 Shift total --> 126
$sql3 = "SELECT date, startTime, endTime, total FROM timeSheet WHERE userName='$user_name'";
$sql4 = "SELECT SUM(total) FROM timeSheet WHERE userName='$user_name'";
echo "<table border='1'>
     <tr>
     <th>date</th>
     <th>start</th>
     <th>end</th>
     <th>total</th>
     </tr>";
foreach ($conn->query($sql3) as $row) { 
    $start = explode (" ", $row['startTime']);
    $end = explode(" ", $row['endTime']);
    if ($start[1] == '00:00:00'){
        $start[1] = '0';
    }else if ($end[1] == '00:00:00'){
        $end[1] = '0';
    }else{        
        $start[1] = date("h:i:s A", strtotime($start[1]));
        $end[1] = date("h:i:s A", strtotime($end[1]));
    }
    echo "<tr>
     <th>" . $row['date'] . "</th>
     <th>" . $start[1] . "</th>
     <th>" . $end[1] . "</th>
     <th>" . $row['total'] . "</th>
     </tr>";
}
foreach ($conn->query($sql4) as $row) { 
    echo "<tr>
            <th></th>
            <th></th>
            <th>SUM</th>
            <th>". $row['SUM(total)']  . "</th>
        </tr>";
}
echo "</table>";

您可以使用TIME函数:

$sql4 = "SELECT TIME(SUM(total)) AS totalSum FROM timeSheet WHERE userName='$user_name'";
//....
foreach ($conn->query($sql4) as $row) { 
echo "<tr>
        <th></th>
        <th></th>
        <th>SUM</th>
        <th>". $row['totalSum'] . "</th>
    </tr>";
}

提示:实际上,在数据库中不需要total列。你可以使用i.e.

SELECT date, startTime, endTime, TIMEDIFF(startTime, endTime) AS total FROM timeSheet WHERE userName='$user_name'"

SELECT TIME(SUM(TIMEDIFF(startTime, endTime))) AS totalSum FROM timeSheet WHERE userName='$user_name'

更多信息请查看:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff