PostgreSQL查询：判断商家当前或指定时间是否营业

表结构 #

示例数据 #

每行存储商家某一工作日的营业与打烊时间，示例如下：

该商家周一至周四（day 0-3）18:00-23:00营业，周五（day4）和周六（day5）营业时间跨次日。

问题与错误SQL #

需要编写单条SQL语句判断商家当前或指定时间是否营业，尝试了以下查询但结果错误：

select count(*)
from (
select *
from business_hours bh
where 
    bh.business_id = 1
    and bh.day =  extract(dow from now()) - 1
union all
select *
from business_hours bh
where 
    bh.business_id = 1
    and bh.day = extract(dow from now()) - 1
) a
where
    ("from" < "to" and now()::time between "from" and "to")
    or ("from" > "to" and now()::time not between "to" and "from")

错误分析 #

1. 字段名不匹配：SQL中使用了"from"和"to"，但表中对应字段是open和close，字段名错误。
2. 星期几偏移逻辑错误：PostgreSQL中extract(dow from now())返回0代表周日，而表中0代表周一，直接减1会导致周日时得到负数，逻辑失效。
3. 跨天场景未覆盖：跨天的营业时段（如18:00到次日01:00），凌晨时段属于前一天的营业范围，原SQL未处理该情况。
4. 冗余查询：union all重复查询同一条件的表数据，完全无意义，只会增加无效计算。

修正后的SQL #

1. 判断当前时间是否营业 #

SELECT EXISTS (
    SELECT 1
    FROM business_hours bh
    WHERE bh.business_id = 1
      AND (
          -- 场景1：当天营业且时间在open和close之间（不跨天）
          (
              bh.day = (extract(dow from now()) - 1 + 7) % 7
              AND bh.open <= bh.close
              AND now()::time BETWEEN bh.open AND bh.close
          )
          OR
          -- 场景2：当天营业且时间在open之后（跨天晚时段）
          (
              bh.day = (extract(dow from now()) - 1 + 7) % 7
              AND bh.open > bh.close
              AND now()::time >= bh.open
          )
          OR
          -- 场景3：前一天营业且时间在close之前（跨天凌晨时段）
          (
              bh.day = (extract(dow from now()) - 2 + 7) % 7
              AND bh.open > bh.close
              AND now()::time <= bh.close
          )
      )
) AS is_open;

2. 判断指定时间是否营业（替换'2024-05-18 00:30:00'为目标时间） #

SELECT EXISTS (
    SELECT 1
    FROM business_hours bh
    WHERE bh.business_id = 1
      AND (
          -- 场景1：指定日期对应星期营业且时间在open和close之间（不跨天）
          (
              bh.day = (extract(dow from '2024-05-18 00:30:00'::timestamp) - 1 + 7) % 7
              AND bh.open <= bh.close
              AND '2024-05-18 00:30:00'::time BETWEEN bh.open AND bh.close
          )
          OR
          -- 场景2：指定日期对应星期营业且时间在open之后（跨天晚时段）
          (
              bh.day = (extract(dow from '2024-05-18 00:30:00'::timestamp) - 1 + 7) % 7
              AND bh.open > bh.close
              AND '2024-05-18 00:30:00'::time >= bh.open
          )
          OR
          -- 场景3：指定日期前一天对应星期营业且时间在close之前（跨天凌晨时段）
          (
              bh.day = (extract(dow from '2024-05-18 00:30:00'::timestamp) - 2 + 7) % 7
              AND bh.open > bh.close
              AND '2024-05-18 00:30:00'::time <= bh.close
          )
      )
) AS is_open;

说明 #

• 使用EXISTS替代count(*)，更高效，直接返回布尔值表示营业状态。
• (extract(dow from ...) - 1 +7) %7处理星期偏移问题，确保结果始终在0-6范围内，避免负数。
• 分三种场景覆盖所有营业情况，包括不跨天当天营业、跨天晚时段营业、跨天凌晨时段营业。

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