mysql总工作时间 [英] mysql total working hours

问题描述

id     title                              start                    end
1   Doing Coding for this project.  2013-04-02 02:00:00     2013-04-02 04:00:00
2   Doing Coding for this project.  2013-04-02 04:00:00     2013-04-02 06:00:00
3   Doing Coding for this project.  2013-04-02 06:00:00     2013-04-02 06:30:00

我上面有MySQL个数据库表记录.现在我想获取hours.

I have above MySQL database table record. Now i want to get the total number of hours.

我正在开发TimeSheet Management Application，并且我们需要显示总工作时间，包括员工的分钟和秒钟. (即根据我分享的数据04:30:00)

我尝试了什么?

SELECT HOUR(TIMEDIFF(end,start)) AS 'totalHour'，但仅适用于每一行，而不适用于所有记录.

SELECT HOUR(TIMEDIFF(end,start)) AS 'totalHour' but works only for each row not on all records.

我也尝试过TIMESTAMPDIFF.

这可能吗?

编辑

从别人那里得到的答案中，我尝试了每个人，但是每次我得到4或4.5000时，它都应该返回06:30:00.

From the answer i have received from people i have tried every single of them but everytime i just get 4 or 4.5000 but it should return 06:30:00.

推荐答案

尝试此查询

SELECT
 id, 
 title, 
 TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF( end, start)))), "%h:%i") AS diff
FROM 
 tbl1
GROUP BY  
 title

根据您给出的答案，数据应为4:30.请交叉检查您的记录.

According to the data that you have given answer should be 4:30. Pl cross check in you records.

FIDDLE

FIDDLE

这篇关于mysql总工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！