求和DATETIMES和分组

Question

这是我的DATETIMEs in和out表。我想对每个p_ID的TIMEDIFFs求和，并按p_ID分组。

t_ID p_ID   t_IN                     t_OUT
1    1      2011-07-13 18:54:56      2011-07-13 20:16:12
2    1      2011-07-14 09:26:56      2011-07-14 09:46:02
3    1      2011-07-14 10:06:39      2011-07-14 10:56:31
4    3      2011-07-14 13:07:04      2011-07-14 13:58:35

我已经尝试了一些MySQL命令无效…我的最后一次尝试是这样的:

SELECT p_ID, TIME_FORMAT(SUM(TIMEDIFF(t_OUT,t_IN)),'%H:%i') AS time FROM timeclock GROUP BY p_ID

我猜我的命令不是每个记录每个p_ID求和…任何帮助吗?

Answer 1

figure out:

$timeDiffReq = mysql_query("CREATE VIEW totals AS SELECT p_ID, TIME_TO_SEC(TIMEDIFF(t_OUT,t_IN)) AS time FROM timeclock");
$timeTotalReq = mysql_query("SELECT p_ID, SEC_TO_TIME(SUM(time)) AS timetotals FROM totals GROUP BY p_ID");
while($row = mysql_fetch_array($timeTotalReq)) {
echo "<div class="clockInOut"><div id="name">" . $row['p_ID'] . " - " . $row['timetotals'] . "</div></div>";}
$timeTotalDrop = mysql_query("DROP VIEW totals");

Answer 2

试试这个:

SELECT p_ID,
       SUM(TIMESTAMPDIFF(HOUR, t_OUT, t_IN)) AS time
FROM timeclock
GROUP BY p_ID;

现在只有小时，不过你可以用分钟来代替。

Answer 3

你很接近了，你只需要一个两步的方法，因为格式化需要在获得SUM之后进行，否则它会妨碍聚合时间差。
试试这个:

SELECT p_ID, TIME_FORMAT(minutes,'%H:%i') as time
FROM (SELECT p_ID, SUM(TIMESTAMPDIFF(MINUTE, t_OUT, t_IN)) AS minutes
    FROM timeclock
    GROUP BY p_ID) x;

不确定TIME_FORMAT的格式，但是这个查询应该很接近。