如何获得日期差异的总和？

Question

my data in table is like this

ds_start_time -> 09:00:00 , ds_end_time-> 09:30:00
ds_start_time -> 09:00:00 , ds_end_time-> 10:30:00
ds_start_time -> 10:00:00 , ds_end_time-> 10:30:00
ds_start_time -> 09:30:00 , ds_end_time-> 10:30:00

i want the difference of date and sum that difference for that i put this in query

SELECT SUM(TIMEDIFF(ds_end_time,ds_start_time)) 
FROM www
WHERE abc='123'........ 

but at the end i am getting 2058800.000000 . From this how can i get total hrs mins and sec. Or any other method .Plz suggest.

Answer 1

It's easy to do this in MySQL if you know how:

SELECT SEC_TO_TIME(SUM
    (TIME_TO_SEC(ds_end_time) - TIME_TO_SEC(ds_start_time))
                 ) AS timediff     
 FROM www 
 WHERE abc = '123'

Note that if there are many rows that have abc = '123' then you will probably get double counts, in that cause you migth want to try:

SELECT SEC_TO_TIME(
    (MAX(TIME_TO_SEC(ds_end_time)) - MIN(TIME_TO_SEC(ds_start_time)))
                 ) AS timediff     
 FROM www 
 WHERE abc = '123'