MySQL: Compute difference of two timestamps in different table rows

user_2678716 0
Viewed 272

I keep on looking around StackOverflow for a similar question but it seems that I can't find one. I would like to know the difference between timestamps in different rows grouped by employee ID.

Time Logs table:

id        timestamp             log_type
 1    2019-06-19 12:34:50        log_in
 2    2019-06-19 13:12:46       start_break 
 3    2019-06-19 13:13:56        end_break
 4    2019-06-19 17:23:40       start_break
 5    2019-06-19 17:44:36        end_break
 6    2019-06-19 19:00:04       start_break
 7    2019-06-19 19:03:17         end_break
 8    2019-06-19 20:05:54        log_out

What I'm trying to accomplish is to calculate all duration of breaks. In this case, 1st break (id #2 and #3) is 1 minute and 10 seconds, 2nd break (id #4 and #5) is 20 minutes and 56 seconds, 3rd break (id #6 and #7) is 3 minutes and 13 seconds thus with the total of 25 minutes and 19 seconds.

Thanks for helping out! Much appreciated.

mysql sql
1 Answers

You can try below -

DEMO

select SEC_TO_TIME(sum(diff)) as result from
(
select  
timestampdiff(second,min(case when log_tpe='start_break' then timestamps end) ,
min(case when log_tpe='end_break' then timestamps end)) as diff
from t
group by date(timestamps),hour(timestamps)
)A

OUTPUT:

result
00:25:19
user_69148640
Related