Get total hours worked in a day mysql

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.

---

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'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 |
+---------+------------+--------------+

---

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.