PostgreSQL查询异常：早于12点时无法提取次日08:30起的时段数据

问题场景 #

现有public.queue_calendar表存储每周各时段的预约槽位配置，当前查询存在逻辑错误：

• 当当前时间+2小时偏移后≥12:00时，可正确提取当日满足occupied_slots < total_slots的时段数据
• 当偏移后时间<12:00时，要求提取次日从08:30开始的符合条件数据，但实际返回结果从10:00左右开始

错误原因分析 #

核心错误出在偏移时间<12:00的过滤分支：
查询中错误地使用了基于当前时间+2小时计算的current_time_slot.start_time来过滤次日的时间槽。例如当前时间为8:00，加2小时后是10:00，过滤条件time_slot >= current_time_slot.start_time::TIME会直接排除次日08:30-09:30的所有时段，导致结果从10:00开始。

修正方向 #

• 调整时间过滤逻辑：当偏移后时间<12:00时，次日的时间过滤条件固定为time_slot >= '08:30'，不再依赖当前时间计算的start_time
• 简化星期匹配逻辑：用更简洁的方式处理次日星期的匹配，避免嵌套CASE语句的冗余和潜在错误

修正后的查询语句 #

WITH time_context AS (
    SELECT
        CURRENT_TIMESTAMP + INTERVAL '2 hour' AS offset_time,
        -- 计算目标星期：偏移时间<12:00则取次日的星期，否则取当日
        CASE
            WHEN CURRENT_TIMESTAMP + INTERVAL '2 hour' < CURRENT_DATE + INTERVAL '12 hour'
            THEN (EXTRACT(DOW FROM CURRENT_TIMESTAMP) + 1) % 7
            ELSE EXTRACT(DOW FROM CURRENT_TIMESTAMP)
        END AS target_dow
)
SELECT
    day_of_week,
    TO_CHAR(time_slot, 'HH24:MI') AS time_slot,
    total_slots,
    occupied_slots
FROM (
    SELECT
        qc.day_of_week,
        qc.time_slot,
        LEAD(qc.time_slot) OVER (PARTITION BY qc.day_of_week ORDER BY qc.time_slot) AS next_time_slot,
        qc.total_slots,
        qc.occupied_slots
    FROM queue_calendar qc
    CROSS JOIN time_context tc
    WHERE
        -- 匹配目标星期（将星期字符串转为DOW数字）
        CASE qc.day_of_week
            WHEN 'Sunday' THEN 0
            WHEN 'Monday' THEN 1
            WHEN 'Tuesday' THEN 2
            WHEN 'Wednesday' THEN 3
            WHEN 'Thursday' THEN 4
            WHEN 'Friday' THEN 5
            WHEN 'Saturday' THEN 6
        END = tc.target_dow
        AND
        -- 根据偏移时间选择对应的起始时间过滤
        (
            (tc.offset_time < CURRENT_DATE + INTERVAL '12 hour' AND qc.time_slot >= '08:30'::TIME)
            OR
            (tc.offset_time >= CURRENT_DATE + INTERVAL '12 hour' AND qc.time_slot >= '08:30'::TIME)
        )
        AND qc.occupied_slots < qc.total_slots
) AS subquery
WHERE next_time_slot IS NULL OR CURRENT_TIME < next_time_slot
ORDER BY day_of_week, time_slot;

额外优化说明 #

• 将时间计算逻辑整合到time_contextCTE中，提升可读性
• 使用%7处理星期的循环（比如周日次日是周一）
• 明确区分日期和时间的比较，避免纯时间比较的歧义

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