# Mysql – Total hours worked per employee per day including overnight sessions

aggregateMySQLrunning-totalssum

I have a table like this:

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |      state      |
+--------------+---------------------+-----------------+
(      4       | 2014-03-01 11:12:00 |         0       )
(      5       | 2014-03-01 12:28:06 |         0       )
(      4       | 2014-03-01 12:50:07 |         1       )
(      4       | 2014-03-01 13:38:00 |         0       )
(      5       | 2014-03-01 13:28:06 |         1       )
(      4       | 2014-03-01 18:42:15 |         1       )
(      4       | 2014-03-02 08:11:08 |         0       )
(      4       | 2014-03-02 13:26:11 |         1       )
(      5       | 2014-03-02 14:16:15 |         0       )
(      4       | 2014-03-02 16:16:15 |         0       )
(      5       | 2014-03-02 17:48:21 |         1       )
(      4       | 2014-03-02 19:39:03 |         1       )
(      5       | 2014-03-02 20:16:15 |         0       )
(      5       | 2014-03-03 04:16:15 |         1       )
+--------------+---------------------+-----------------+


primary key omitted for brevity – please see http://www.sqlfiddle.com/#!9/30503/1

0 means login – 1 means logout. There can be several logins/logouts per user per day or period of days if for example an employee works overnight (as employee 5 does between 2014-03-02 and 2014-03-03 )

I am trying to get the total sum of hours worked per employee per day/shift (e.g. some shifts will span over two days):

1      2014-03-02   total hours worked    08:32:00


I came up with this query:

SELECT CONCAT(
MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked,
empid,
Date(time)
FROM emplog T
GROUP BY empid, Date(time)


Which only gave me the time elapsed between the min login and max logout.

From an earlier question I learned how to get the total working hours per employee per day which does not include night shifts:

select empid, work_dt,
from (
select empid, date(time) as work_dt, time as login
, coalesce(
(select min(time)
from emplog as b
where a.empid = b.empid
and date(a.time) = date(b.time)
and b.time >= a.time
and b.state = 1
), now()) as logout
from emplog as a
where a.state = 0
) as t
group by empid, work_dt;


Still haven't figured out a way to factor in night shifts – looking forward to your input.

There will be employees working during the weekend. Holidays will not be a problem; neither will daylight saving time be.

The application does not have a check in place to prevent out of sync clock punches? For example, clock in/out records out of time-sequence or duplicated due to employee error. I already took care of those kinds of problems in the code I wrote to connect with the device and retrieve the raw data – might be better to handle all the calculations in my code.

Night shifts count 100 % to the day the shift started on. For example, if you start work on 02.01 before midnight, the total working hours are saved with the 02.01 as the date. If you start after midnight, the total working hours are saved with the 03.01 as date.

If there is no matching logout I manually log the user out with a predefined number of hours in my code that prepares the raw data for the MySQL database.

Please check if the performance is sufficient:

SELECT empid
,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
FROM emplog b
WHERE b.empid = a.empid
AND b.time  > a.time
AND b.state = 1
)))) date_worked
FROM emplog a
WHERE state=0
GROUP BY empid;


Edit

To aggregate per day:

SELECT empid
,DATE(time) day
,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
FROM emplog b
WHERE b.empid = a.empid
AND b.time  > a.time
AND b.state = 1
)))) date_worked
FROM emplog a
WHERE state=0
GROUP BY empid, DATE(time);