mysql工作时间获取,在一天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.