MySQL转换日期格式(m/d/Y转Y/m/d)及计算加班时长问题

核心问题分析 #

你的attDate字段是字符串类型，格式为MM/DD/YYYY HH.mm，直接对字符串做时间计算、分组会因为字符串排序规则和时间逻辑不符导致错误，同时原SQL存在字段拼写错误、加班时长可能为负的问题。

修正后的SQL语句 #

SELECT
    EMPID,
    DATE_FORMAT(STR_TO_DATE(MIN(attDate), '%m/%d/%Y %H.%i'), '%Y/%m/%d') AS work_date,
    DATE_FORMAT(STR_TO_DATE(MIN(attDate), '%m/%d/%Y %H.%i'), '%H:%i') AS TimeIN,
    DATE_FORMAT(STR_TO_DATE(MAX(attDate), '%m/%d/%Y %H.%i'), '%H:%i') AS TimeOUT,
    GREATEST(
        TIMESTAMPDIFF(
            MINUTE,
            STR_TO_DATE(MIN(attDate), '%m/%d/%Y %H.%i'),
            STR_TO_DATE(MAX(attDate), '%m/%d/%Y %H.%i')
        ) - 9*60,
        0
    ) AS 'OT Minutes'
FROM attTable
WHERE STR_TO_DATE(attDate, '%m/%d/%Y %H.%i') BETWEEN '2023-05-01 00:00:00' AND '2023-06-01 23:59:59'
GROUP BY EMPID, DATE_FORMAT(STR_TO_DATE(attDate, '%m/%d/%Y %H.%i'), '%Y/%m/%d')
ORDER BY EMPID;

关键改动说明 #

• 字符串转时间类型：用STR_TO_DATE(attDate, '%m/%d/%Y %H.%i')把字符串转换成MySQL可识别的datetime类型，其中%m对应两位月份、%d对应两位日期、%H对应24小时制小时、%i对应分钟，匹配原格式里的.分隔符。
• 日期格式转换：通过DATE_FORMAT(..., '%Y/%m/%d')将转换后的时间格式化为Y/m/d的目标格式。
• 修复分组字段：原SQL的emp_id是拼写错误，改为表中实际字段EMPID。
• 避免负加班时长：用GREATEST(计算值, 0)确保当工作时长不足9小时时，加班时长显示为0，不会出现负数。
• 准确的时间范围筛选：将where条件中的字符串比较改为转换后的datetime类型比较，避免隐式转换导致的筛选错误。

测试结果 #

针对你提供的测试数据，执行上述SQL会得到如下结果：

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