PostgreSQL从DateTime字段提取时间计算迟到时长的结果异常修复

问题原因 #

1. 两个time类型直接相减得到的interval如果为负值，直接格式化会输出不符合预期的负时长，早到场景下会显示负的小时/分钟数。如果是跨零点的班次，打卡时间落在次日凌晨时，直接相减得到的差值会完全不符合实际迟到逻辑。
2. 原有格式串混用HH24和PM属于冗余语法，虽然不会直接报错，但不符合格式规范，存在隐式转换风险。

修复方案 #

适用非跨零点班次（班次起止时间在同一天） #

直接通过GREATEST过滤负差值，早到场景迟到时长记为0，修复后的SQL如下：

SELECT
    TO_CHAR(s.start_time AT TIME ZONE 'Asia/Singapore','HH24:MI:SS')  AS roster_starttime,
    TO_CHAR(c.time_server AT TIME ZONE 'Asia/Singapore','HH24:MI:SS') AS clock_time,
    TO_CHAR(
        GREATEST(
            (c.time_server AT TIME ZONE 'Asia/Singapore')::time - (s.start_time AT TIME ZONE 'Asia/Singapore')::time,
            '0 minutes'::interval
        ),
        'FMHH "Hours" FMMI "minutes"'
    ) AS lateness_minutes
FROM
    employee_info e
INNER JOIN shifts s ON s.employee_id = e.employee
INNER JOIN clock c ON c.shift = s.id

格式串中增加FM前缀可以自动去掉数值前的补零，避免出现00 Hours 11 minutes这类显示问题。

适用跨零点班次 #

如果存在班次开始时间在当日深夜、结束时间在次日凌晨的场景，需要额外处理次日打卡的差值逻辑：

SELECT
    TO_CHAR(s.start_time AT TIME ZONE 'Asia/Singapore','HH24:MI:SS')  AS roster_starttime,
    TO_CHAR(c.time_server AT TIME ZONE 'Asia/Singapore','HH24:MI:SS') AS clock_time,
    TO_CHAR(
        GREATEST(
            CASE
                WHEN (c.time_server AT TIME ZONE 'Asia/Singapore')::time >= (s.start_time AT TIME ZONE 'Asia/Singapore')::time
                THEN (c.time_server AT TIME ZONE 'Asia/Singapore')::time - (s.start_time AT TIME ZONE 'Asia/Singapore')::time
                ELSE (c.time_server AT TIME ZONE 'Asia/Singapore')::time + INTERVAL '1 day' - (s.start_time AT TIME ZONE 'Asia/Singapore')::time
            END,
            '0 minutes'::interval
        ),
        'FMHH "Hours" FMMI "minutes"'
    ) AS lateness_minutes
FROM
    employee_info e
INNER JOIN shifts s ON s.employee_id = e.employee
INNER JOIN clock c ON c.shift = s.id

额外优化：输出数值型迟到分钟数 #

如果需要后续对迟到时长做统计计算，可以直接提取总分钟数的数值类型：

EXTRACT(EPOCH FROM (
    GREATEST(
        (c.time_server AT TIME ZONE 'Asia/Singapore')::time - (s.start_time AT TIME ZONE 'Asia/Singapore')::time,
        '0 minutes'::interval
    )
)) / 60 AS lateness_total_minutes

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