Postgres中判断时间戳不在两个时间段内的SQL实现方法

核心逻辑 #

需要同时满足两个判定规则：

• 目标时间timestamp不落在start_hour1、end_hour1构成的时间段内
• 若start_hour2、end_hour2不为空，则timestamp不落在二者构成的时间段内；若两个字段任意一个为NULL，默认第二个时间段不存在，直接判定为满足条件

场景1：时间段不跨天（start_hour < end_hour，如9点到18点） #

通用SQL写法如下，区间默认采用闭区间（包含起止时间），可根据业务需求调整比较运算符修改区间开闭规则：

SELECT *
FROM 你的表名
WHERE
  -- 排除第一个时间段内的记录
  NOT (
    `timestamp` >= start_hour1
    AND `timestamp` <= end_hour1
  )
  -- 处理第二个时间段的NULL兼容判断
  AND (
    start_hour2 IS NULL
    OR end_hour2 IS NULL
    OR NOT (
      `timestamp` >= start_hour2
      AND `timestamp` <= end_hour2
    )
  );

场景2：时间段支持跨天（start_hour > end_hour，如22点到次日3点） #

跨天时间段的命中逻辑为「时间大于等于开始时间，或小于等于结束时间」，对应排除逻辑调整如下：

SELECT *
FROM 你的表名
WHERE
  -- 排除第一个跨天时间段内的记录
  NOT (
    start_hour1 > end_hour1
    AND (`timestamp` >= start_hour1 OR `timestamp` <= end_hour1)
  )
  -- 处理第二个跨天时间段的NULL兼容判断
  AND (
    start_hour2 IS NULL
    OR end_hour2 IS NULL
    OR NOT (
      start_hour2 > end_hour2
      AND (`timestamp` >= start_hour2 OR `timestamp` <= end_hour2)
    )
  );

注意事项 #

• 如果timestamp是完整的datetime类型，而start_hour/end_hour是0-23的小时整数值，需要先提取时间字段的小时部分再做比较：
  • MySQL用HOUR(\timestamp`)`取值
  • PostgreSQL用EXTRACT(HOUR FROM "timestamp")取值
  • SQL Server用DATEPART(HOUR, [timestamp])取值
• 如果业务规则要求start_hour2和end_hour2同时为NULL才算第二个时间段不存在，只需要把判断条件里的OR改成AND即可。
• 若需要左闭右开/左开右闭区间，直接调整对应的>=/<=运算符为>/<即可。

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