Get total hours worked between 2 days mysql

Question

I have a MySQL table where employee login and logout timings are recorded. Here in the in-out column 1-represents login and 0-represents logout.

  [id]   [User_id]           [Date_time]                 [in_out]
    1       1          2011-01-20 18:01:03                  1
    2       1          2011-01-20 19:30:43                  0
    3       1          2011-01-20 20:46:23                  1
    4       1          2011-01-21 00:42:45                  0

Is it possible to retrieve total hours worked in a day (between 2 days) by a user using single query?

The same Question it's a copy of Get total hours worked in a day mysql and solution:

  SELECT `User_id`, time(sum(`Date_time`*(1-2*`in_out`))) 
  FROM `whatever_table` GROUP BY `User_id`;

But the solution needs to be different when the employee start working in a day and go out on the next day.

Do you mean time between id 1-2 + time between id 3-4? If yes, you should do that work on server side, with PHP for example and a loop through data returned by your query. — sdespont, Apr 17 '14 at 21:28

score 1 · Accepted Answer · answered Aug 13 '14 at 14:05

You can achieve this using given stored procedure. Consider your table name EventLog.

DELIMITER $$
CREATE PROCEDURE `GET_TOTAL_LOGIN_TIME`(
    IN  startDate DATETIME,
    IN  endDate DATETIME,
    IN  userId INT(11)
)
BEGIN
    select
    (sum(
case when(e2.Date_time <= startDate) then 0 else 
case when(e1.Date_time >= endDate) then 0 else
case when(e1.Date_time >= startDate && e2.Date_time <= endDate) then 
            TIME_TO_SEC(TIMEDIFF(e2.Date_time, e1.Date_time))/60 else 
case when(e1.Date_time <= startDate && e2.Date_time <= endDate) then 
            TIME_TO_SEC(TIMEDIFF(e2.Date_time, startDate))/60 else 
case when(e1.Date_time >= startDate && e2.Date_time >= endDate) then 
            TIME_TO_SEC(TIMEDIFF(endDate,e1.Date_time))/60
end end end end end
)) as loginTimeInMin
    from
        ((EventLog e1
        left join EventLog e2 ON (((e1.User_id = e2.User_id) and (e2.in_out = 0) and (e1.Date_time < e2.Date_time))))
        left join EventLog e3 ON (((e1.User_id = e3.User_id) and (e1.Date_time < e3.Date_time) and (e3.Date_time < e2.Date_time))))
    where
        ((e1.in_out = 1) and isnull(e3.Date_time)) and e2.Date_time is not null
        AND e1.User_id = userId
        AND userRole.userRoleId = roleId
        AND userRole.userLoginId = userId
    group by e1.User_id;
END;

Thanks @vishal..I want the same thing and it works perfect for me. :) — Charvee Shah, Aug 13 '14 at 14:08

score 0 · Answer 2 · answered Apr 17 '14 at 21:39

0

You can get the number of seconds worked like this:

 SELECT `User_id`, sum(unix_timestamp(`Date_time`)*(1-2*`in_out`))
 FROM `whatever_table`
 GROUP BY `User_id`;

Then you can convert the seconds to whatever you want.

answered Apr 17 '14 at 21:39

matt

1,947
1
19
29

Get total hours worked between 2 days mysql

2 Answers2