PostgreSQL时间类型列比较异常：单条应匹配记录未关联

问题根源 #

你遇到的是PostgreSQL中time类型跨午夜区间的between运算符逻辑陷阱。针对那条未匹配的记录：

• cms表的scheduled_load_start（00:00:00）减去1.5小时，转成time类型后是22:30:00
• scheduled_load_finish（01:00:00）加上2小时，转成time类型后是03:00:00

time类型没有日期维度，是一个00:00:00到23:59:59的循环值。between运算符要求目标值同时满足≥左边界且≤右边界，但这里左边界22:30:00大于右边界03:00:00（跨午夜），01:00:38不满足≥22:30:00，因此整个条件不成立，导致匹配失败。

修复方案 #

方案1：兼容跨午夜区间的time类型判断

替换between为分情况的逻辑判断，覆盖正常区间和跨午夜区间：

a.col1 = cms.col1
AND a.col2 = cms.col2
AND (
    -- 非跨午夜区间：直接用between
    ( (cms.scheduled_load_start - interval '1.5 hours')::time <= (cms.scheduled_load_finish + interval '2 hours')::time )
    AND to_char(a.job_time::timestamp, 'HH24:MI:SS')::time 
        BETWEEN (cms.scheduled_load_start - interval '1.5 hours')::time 
        AND (cms.scheduled_load_finish + interval '2 hours')::time
    OR
    -- 跨午夜区间：匹配左边界之后 或 右边界之前
    ( (cms.scheduled_load_start - interval '1.5 hours')::time > (cms.scheduled_load_finish + interval '2 hours')::time )
    AND (
        to_char(a.job_time::timestamp, 'HH24:MI:SS')::time >= (cms.scheduled_load_start - interval '1.5 hours')::time
        OR to_char(a.job_time::timestamp, 'HH24:MI:SS')::time <= (cms.scheduled_load_finish + interval '2 hours')::time
    )
)

方案2：转换为带日期的timestamp统一比较

给所有time值附加一个固定基准日期，将其转换为timestamp类型，避免跨午夜的歧义：

a.col1 = cms.col1
AND a.col2 = cms.col2
AND ('2000-01-01'::date + to_char(a.job_time::timestamp, 'HH24:MI:SS')::time)
    BETWEEN ('2000-01-01'::date + cms.scheduled_load_start::time) - interval '1.5 hours'
    AND ('2000-01-01'::date + cms.scheduled_load_finish::time) + interval '2 hours'

这里选择2000-01-01作为基准日期只是为了统一时间维度，不影响时分秒的相对比较结果。

验证测试 #

针对那条异常数据：

• 转换后的目标时间：2000-01-01 01:00:38
• 左边界：2000-01-01 00:00:00 - 1.5h = 1999-12-31 22:30:00
• 右边界：2000-01-01 01:00:00 + 2h = 2000-01-01 03:00:00
  目标时间明显处于该区间内，会正确匹配。

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