MySQL查询：按员工每日统计考勤并生成TimeIN、TimeOUT及OT Minutes字段

问题背景 #

现有attdata表，结构及数据如下：

需要查询某一个月的记录，按以下格式输出：

规则说明：

• 工作满9小时后，超出部分按分钟计算加班时长
• 单日多条记录时，TimeOUT取当日最晚考勤时间

解决方案 #

以下是满足需求的MySQL查询语句：

SELECT
    EMPID,
    DATE_FORMAT(MIN(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i')), '%Y/%m/%d %H.%i') AS TimeIN,
    DATE_FORMAT(MAX(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i')), '%Y/%m/%d %H.%i') AS TimeOUT,
    GREATEST(
        TIMESTAMPDIFF(MINUTE, MIN(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i')), MAX(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i'))) - 9*60,
        0
    ) AS `OT Minutes`
FROM attdata
WHERE DATE(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i')) BETWEEN '2023-05-01' AND '2023-05-31' -- 替换为目标月份的起止日期
GROUP BY EMPID, DATE(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i'))
ORDER BY EMPID, DATE(STR_TO_DATE(attDate, '%Y/%m/%d %H.%i'));

语句解释 #

1. 时间格式转换：使用STR_TO_DATE(attDate, '%Y/%m/%d %H.%i')将字符串格式的attDate转换为MySQL可识别的datetime类型，适配原数据中小时与分钟用.分隔的格式。
2. 分组逻辑：按EMPID和考勤日期的日期部分分组，确保每个员工每日仅生成一条汇总记录。
3. TimeIN/TimeOUT获取：用MIN()提取当日最早考勤时间作为TimeIN，MAX()提取当日最晚考勤时间作为TimeOUT，再通过DATE_FORMAT()转换回原格式展示。
4. 加班时长计算：
   • TIMESTAMPDIFF(MINUTE, TimeIN, TimeOUT)计算当日工作总分钟数
   • 减去9小时对应的540分钟，用GREATEST(..., 0)保证加班时长不会为负数（工作不足9小时时加班时长为0）
5. 月份筛选：通过WHERE子句指定目标月份的起止日期，按需替换即可。

内容的提问来源于stack exchange，提问作者mafaz