Get total hours worked in a day mysql

I have a mysql table where employee login logout timings are noted.Here in the in-out coloumn 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 retreieve total hours worked in a day by a user using single query?? I tried a alot but all in vain.I can do this in php using array but unable to do so using single query.

Thanks in advance...

This question and answers originated from www.stackoverflow.com
Question by (1/20/2011 9:06:37 AM)

Answer

 
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.

Answer by
Find More Answers
Related Topics  mysql  sql  datetime
Related Questions