mysql 营业时间,mysql总工作时间

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

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

I am developing TimeSheet Management Application and we need to display total working hours with minutes and second of employee. (i.e 04:30:00 according to data i share)

what i have tried?

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

I have also tried TIMESTAMPDIFF.

Is this possible?

EDIT

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.

解决方案

Try this query

SELECT

id,

title,

TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF( end, start)))), "%h:%i") AS diff

FROM

tbl1

GROUP BY

title

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