mysql - 计算MySQL中的时间差

Question

SELECT empno , date_created , 
min(CASE WHEN status = 0 THEN time_created END) time_in,
max(CASE WHEN status = 1 THEN time_created END) time_out
FROM biometrics
WHERE empno = 3
GROUP BY empno , date_created 

在此语法中，它仅确定员工的先进后出，但我想问一下我如何计算员工的第一个 IN 和最后一个 Out 之间的时间差或他/她的总时间。

例如，员工的第一个上类时间是上午 9:00:00，最后一个下类时间是下午 16:00:00。我如何计算该员工的总工作时数？

Answer 1

请尝试以下操作:

select 
  empno, 
  date_created, 
  time_in, 
  time_out, 
  time_format(timediff(time_out, time_in), '%H:%i') as total_time 
from 
(
  SELECT empno, date_created, 
    min(CASE WHEN status = 0 THEN time_created END) time_in,
    max(CASE WHEN status = 1 THEN time_created END) time_out
  FROM biometrics
  WHERE empno = 3 
  GROUP BY empno, date_created
) t1;