Получить общее количество часов работы в день mysql

У меня есть таблица MySQL, в которой записаны учетные записи пользователей и время выхода из системы. Здесь в столбце ввода-вывода 1 отображается логин и 0-вывод.

  [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

Можно ли получить общее количество часов, отработанных за день пользователем, использующим один запрос?

Я пробовал много, но все напрасно. Я могу сделать это в PHP, используя массив, но не могу сделать это, используя один запрос.

Ответ 1

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

Термин (1-2 * `in_out`) дает каждому событию входа -1 коэффициент, а каждое событие выхода - +1. Функция sum принимает сумму столбца Date_time, а GROUP BY `User_id` делает сумму для каждого другого пользователя.

Ответ 2

Правило Руди правильное. Если вы хотите, чтобы часы были десятичными, используйте UNIX_TIMESTAMP() в поле date_time, иначе возвращаемый тип будет DATETIME (трудно разобрать). Здесь результат для ваших первых 4 строк данных плюс еще 2 дня на другой день:

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

Ответ 3

Попробуйте:

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]

Я не знаю синтаксиса MySQL, поэтому, если кто-то находит ошибки, исправьте их. Может быть, не получилось. Например, если вы вошли в систему пользователя. В этом случае вы можете фильтровать только пользователей с одинаковым числом 1 и 0 в поле [in_out].