弹性考勤记录查询SQL如何计算员工的实际工作时长？

SELECT
employee_id,
date,
SUM(
CASE
WHEN check_out_time < check_in_time THEN — 跨天情况
TIMESTAMPDIFF(SECOND,
CASE WHEN check_in_time < ’10:00:00′ THEN ’10:00:00′ ELSE check_in_time END,
’23:59:59′
) + TIMESTAMPDIFF(SECOND, ’00:00:00′,
CASE WHEN check_out_time > ’16:00:00′ THEN ’16:00:00′ ELSE check_out_time END
)
/ 3600
WHEN check_out_time >= check_in_time THEN — 非跨天情况
TIMESTAMPDIFF(SECOND,
CASE WHEN check_in_time < ’10:00:00′ THEN ’10:00:00′ ELSE check_in_time END,
CASE WHEN check_out_time > ’16:00:00′ THEN ’16:00:00′ ELSE check_out_time END
) / 3600
ELSE 0
END
) AS work_hours
FROM
attendance_records
WHERE
date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’ AND employee_id = 123
GROUP BY
employee_id,
date;