如何通过SQL查询筛选获取所有未来的门店打烊时间

你当前的实现存在两个核心问题，导致结果混入过去时间、跨周打烊时间错位：

• 硬编码锚定了某一个固定日期的周次做计算，没有和实时当前时间做动态比对，整周的打烊时间不管是过去还是未来全部输出
• 没有对已经早于当前时间的打烊周期做周次偏移，导致周日（weekday=0）、周一（weekday=1）这类周初对应的打烊时间，被固定算到了上一周的过去时间段里

核心逻辑是：对每一条营业规则，同时计算「本周对应规则的打烊时间」「下周对应规则的打烊时间」，自动选取大于当前时间的最近一次打烊结果，过滤掉所有早于当前时间的记录。
你的weekday字段取值（0代表周日）和PostgreSQL内置的周次标识完全匹配，可以直接复用原有周次计算逻辑，只需要增加时间比对和自动偏移的判断即可。

修正后的SQL #

WITH current_base AS (
  -- 以实时当前时间为基准，取本周周一零点作为周计算锚点，替换原写法里硬编码的固定日期
  SELECT date_trunc('week', now())::timestamp AS week_start
)
SELECT
  oh.*,
  CASE
    -- 如果本周算出的打烊时间已经早于当前时间，自动往后偏移7天取下周的打烊时间
    WHEN week_start + make_interval(
      days => oh.weekday,
      mins => oh.opening_minute + oh.shift_length
    ) < now()
    THEN week_start + make_interval(
      days => oh.weekday,
      mins => oh.opening_minute + oh.shift_length
    ) + INTERVAL '7 days'
    -- 否则直接取本周的打烊时间
    ELSE week_start + make_interval(
      days => oh.weekday,
      mins => oh.opening_minute + oh.shift_length
    )
  END AS closes_at
FROM opening_hours oh
CROSS JOIN current_base
ORDER BY closes_at;

效果验证 #

把代码里的now()替换为你举例的测试时间'2021-04-29 08:30'::timestamp，计算结果完全匹配预期：

• weekday=2（周二）到weekday=6（周六）的打烊时间分别为2021-04-30 01:30到2021-05-04 01:30，都晚于测试时间，直接保留
• weekday=0（周日）的基础打烊时间为2021-04-28 01:30，早于测试时间，自动加7天偏移为2021-05-05 01:30
• weekday=1（周一）的基础打烊时间为2021-04-29 01:30，早于测试时间，自动加7天偏移为2021-05-06 01:30

注意事项 #

• 打烊时间跨零点的问题不需要额外处理：直接累加opening_minute + shift_length的分钟数，PostgreSQL会自动完成时间戳的天进位计算，比如540分钟（9点）加990分钟（16.5小时），会自动算出当日25.5点对应的次日1:30，和你当前的计算逻辑完全一致
• 原写法里的date_trunc('week', '2021-04-29'::date - 1)是冗余的：PostgreSQL的date_trunc('week')本身就是以周一作为周起点计算，和你的weekday字段的数值偏移量完全匹配，减去1天的写法没有实际效果，只会增加理解成本

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