13

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 09:30:03                  1
    2       1          2011-01-20 11:30:43                  0
    3       1          2011-01-20 11:45:12                  1
    4       1          2011-01-20 12:59:56                  0
    5       1          2011-01-20 13:33:11                  1
    6       1          2011-01-20 15:38:16                  0
    7       1          2011-01-20 15:46:23                  1
    8       1          2011-01-20 17:42:45                  0

Is it possible to retrieve total hours worked in a day by a user using single query?

I tried a a lot but all in vain. I can do this in PHP using array but unable to do so using single query.

laurent
  • 88,262
  • 77
  • 290
  • 428
Ullas Prabhakar
  • 416
  • 2
  • 10
  • 24
  • 1
    Technically it's not at all possible, but if you're looking for the time between when they clocked in, and when they clocked out, that's possible! – Alan Pearce Jan 20 '11 at 09:29
  • 1
    @Jasie: I was joking about the time spent working not being equal to the difference between clock-in time and clock-out time. – Alan Pearce Jan 20 '11 at 15:46
  • 3
    @Alan, oh, I couldn't tell the difference here between humor and brain misfire ;) – atp Jan 20 '11 at 18:59

3 Answers3

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

The (1-2*`in_out`) term gives every login event a -1 factor and every logout event a +1 factor. The sum function takes the sum of the Date_time column, and GROUP BY `User_id` makes that the sum for each different user is created.

Rudi
  • 19,366
  • 3
  • 55
  • 77
  • When `in_out` is 1, this term is (1-2*1)=-1, which makes the `Date_time` field for this row negative. When `in_out` is 0, the term is (1-2*0)=1, and the `Date_time` row is not altered. So the `sum` function sees a positive value for each log-out, and a negative value for each log-in. So the result of `sum` is every log-out event subtracted by every log-in event. The time function around the sum is used to make the output more clear, since the result of the sum is a date-time value, somewhere near year Jan 1st Year 0. – Rudi Jan 21 '11 at 10:13
  • this is a really creative solution. I love it. – Dario Russo Apr 19 '12 at 18:56
4

Rudi's line of thinking is correct. If you want the hours with decimal, use UNIX_TIMESTAMP() on the date_time field, otherwise the returned type will be a DATETIME (hard to parse). Here's the result for your first 4 lines of data plus 2 more for a different day:

SELECT 
    `user_id`, 
    DATE(`date_time`) AS `date`, 
    SUM(UNIX_TIMESTAMP(`date_time`)*(1-2*`in_out`))/3600 AS `hours_worked`
FROM `test` 
GROUP BY date(`date_time`), `user_id`;
+---------+------------+--------------+
| user_id | date       | hours_worked |
+---------+------------+--------------+
|       1 | 2011-01-20 |       3.2567 |
|       1 | 2011-01-21 |       3.0000 |
+---------+------------+--------------+
atp
  • 30,132
  • 47
  • 125
  • 187
  • You could replace the "date(`date_time`)" in the GROUP BY with just "date" since it was defined in the SELECT Expression already. – Clutch Sep 27 '13 at 16:21
  • @Clutch Any chance you could check on a modified use case I posted? – aVC Aug 25 '22 at 19:56
0

Give this a try:

select [User_id], sum([Time_count])/3600
from (
  select [User_id],
         case when [in_out]=1 then   UNIX_TIMESTAMP([Date_time])
              when [in_out]=0 then - UNIX_TIMESTAMP([Date_time])
              end as [Time_count]
  from my_table
) as a
group by [User_id]

I don't know MySQL syntax so if someone finds errors, please correct them. Maybe could not work. For example if you have a user logged in. In that case you can filter only the users that has the same number of 1's and 0's in the [in_out] field.

bluish
  • 26,356
  • 27
  • 122
  • 180