请求修正员工加班时长查询SQL语句（存在to_char错误）

原SQL问题梳理 #

1. 字段名混用：原语句中Clock_in_date/end_date_time与查询列表里的start_time/end_time不统一，需对齐表中实际字段
2. 时间差格式化错误：直接对interval类型执行to_char会导致格式异常，需用更可靠的方式解析时长
3. 工作日加班逻辑漏洞：未考虑start_time晚于17:00的情况，此时应从start_time而非17:00开始计算加班时长

最终修正语句 #

SELECT 
    user_id,
    start_time,
    end_time,
    CASE
        -- 周一至周五：计算17:00:01之后的加班时长
        WHEN TO_CHAR(start_time, 'DY', 'nls_date_language=english') IN ('MON', 'TUE', 'WED', 'THU', 'FRI')
             AND end_time > TRUNC(start_time) + INTERVAL '17' HOUR THEN
            -- 提取时分秒并拼接为易读格式
            TO_CHAR(EXTRACT(HOUR FROM (end_time - GREATEST(start_time, TRUNC(start_time) + INTERVAL '17' HOUR)))) || '小时' ||
            TO_CHAR(EXTRACT(MINUTE FROM (end_time - GREATEST(start_time, TRUNC(start_time) + INTERVAL '17' HOUR)))) || '分钟' ||
            TO_CHAR(EXTRACT(SECOND FROM (end_time - GREATEST(start_time, TRUNC(start_time) + INTERVAL '17' HOUR)))) || '秒'
        -- 周六、周日：计算全天工作时长
        WHEN TO_CHAR(start_time, 'DY', 'nls_date_language=english') IN ('SAT', 'SUN') THEN
            TO_CHAR(EXTRACT(HOUR FROM (end_time - start_time))) || '小时' ||
            TO_CHAR(EXTRACT(MINUTE FROM (end_time - start_time))) || '分钟' ||
            TO_CHAR(EXTRACT(SECOND FROM (end_time - start_time))) || '秒'
        -- 无加班场景
        ELSE 'no overtime'
    END AS overtime
FROM 
    employee;

适配其他数据库说明 #

如果使用MySQL而非Oracle，需调整时间处理逻辑：

• 星期判断用DAYOFWEEK(start_time)（1=周日，2=周一…7=周六）
• 时间差计算用TIMESTAMPDIFF函数，例如：

  TIMESTAMPDIFF(HOUR, GREATEST(start_time, DATE(start_time) + INTERVAL 17 HOUR), end_time)
  

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