сравнить две даты в одном столбце в MySQL
У нас есть биометрические данные, которые содержат три столбца Идентификатор сотрудника, TimeIn и идентификатор автоинкремента.
Мы должны выяснить часы работы сотрудника, но он может входить и выходить два или три раза в день.
Как узнать часы для его входа и выхода. Там нет колонки, в которой мы говорим и выходить. Мы также сделали разницу max (TimeIn) и min (TimeIn). Но в нашем случае это невозможно из-за того, что сотрудник входит и выходит в любое время и много раз в день.
13 2017-10-31 23:15:11 1 255 1 0
13 2017-10-31 23:15:27 1 255 1 0
3 2017-11-01 05:54:06 1 255 1 0
2 2017-11-01 05:54:31 1 255 1 0
11 2017-11-01 05:55:58 1 255 1 0
1 2017-11-01 05:56:24 1 255 1 0
14 2017-11-01 06:02:23 1 255 1 0
8 2017-11-01 07:54:24 1 255 1 0
8 2017-11-01 07:54:27 1 255 1 0
7 2017-11-01 08:06:37 1 255 1 0
13 2017-11-01 08:29:52 1 255 1 0
10 2017-11-01 09:54:41 1 255 1 0
15 2017-11-01 10:44:17 1 255 1 0
4 2017-11-01 11:00:28 1 255 1 0
14 2017-11-01 11:00:59 1 255 1 0
5 2017-11-01 11:24:50 1 255 1 0
9 2017-11-01 12:46:32 1 255 1 0
6 2017-11-01 12:58:24 1 255 1 0
11 2017-11-01 13:00:18 1 255 1 0
1 2017-11-01 13:00:28 1 255 1 0
2 2017-11-01 16:06:14 1 255 1 0
2 2017-11-01 16:06:24 1 255 1 0
Решение
следите за тем, что нет входов и выходов, вы можете принять только первый вход как IN, а затем как Out.
Вы можете выполнить внутреннее объединение со следующим значением, чтобы получить разницу во времени, а не только нечетное значение фильтрации (даже будет время ожидания)
select a.row, a.empid, timestampdiff(second,a.timein, ifnull(b.timeout,date_add(date(a.timein) , interval 1 day))) timeinsec from
(SELECT @row_num := IF(@prev_value=o.empid,@row_num+1,1) AS Row,
empid ,timein,
@prev_value := o.empid
FROM Table1 o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY o.empid,timein asc
) a
join
(SELECT @row_num := IF(@prev_value2=o.empid,@row_num+1,1) AS Row,
empid ,timein as timeout,
@prev_value2 := o.empid
FROM Table1 o,
(SELECT @row_num := 1) x,
(SELECT @prev_value2 := '') y
ORDER BY o.empid,timein asc) b
on a.empid=b.empid and a.row=b.row-1
where mod(a.row,2)=1
- Datediff изменен на timestampdiff
- нулевая ситуация обработана
Другие решения
SELECT SUM(HOUR(TimeIn)) FROM table_biometric group by DATE(TimeIn),Employee_id
используя запрос, вы получите общее количество часов, отработанных в день на каждого сотрудника.
и после вы можете, если вы хотите, суммировать количество часов за весь день с помощью функции
наконец, вы можете использовать функцию datetime для другой помощи
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
Ну, я не знаю ваших столбцов таблицы. Было бы неплохо иметь результат describe your_table_name запрос.
Но вы можете адаптировать этот запрос для своих нужд:
SELECT
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(t1.your_datetime_column_name))), "%h:%i") AS diff
from table t1
LEFT join table t2
on t1.primary_key_column_name != t2.primary_key_column_name
WHERE t1.primary_key_column_name IS NOT NULL