SQL中如何将event_timestamp与时间戳区间进行大小比较

最优方案说明 #

场景1：时间区间存储在独立数据表中（假设表名为user_time_ranges） #

优先使用EXISTS关联查询，是性能最优的实现方式，只要在两张表的关联字段、时间字段上建立索引，查询效率会非常高。
可根据实际业务需求调整时间判断逻辑，示例代码如下：

SELECT COUNT(s.id) = 0 AS "isEmpty"
FROM status s
WHERE s.id = 22
  AND EXISTS (
    SELECT 1 
    FROM user_time_ranges utr
    WHERE utr.id = s.id
      -- 判断event_timestamp落在任意一个区间内：
      AND s.event_timestamp BETWEEN utr.start_user_timestamp AND utr.end_user_timestamp
      -- 如需判断大于所有区间结束时间，替换为：AND s.event_timestamp > ALL (SELECT end_user_timestamp FROM user_time_ranges WHERE id = 22)
      -- 如需判断小于所有区间开始时间，替换为：AND s.event_timestamp < ALL (SELECT start_user_timestamp FROM user_time_ranges WHERE id = 22)
  )

场景2：时间区间为固定值，可直接硬编码在查询中 #

直接拼接区间判断条件即可，写法简洁，不需要额外关联表：

SELECT COUNT(id) = 0 AS "isEmpty"
FROM status
WHERE id = 22
  AND (
    event_timestamp BETWEEN '2021-05-02 11:23:59.02343' AND '2021-05-04 21:43:39.02343'
    OR event_timestamp BETWEEN '2021-06-01 13:23:59.02343' AND '2021-06-04 13:43:59.02343'
    OR event_timestamp BETWEEN '2021-07-01 23:43:59.02343' AND '2021-07-04 23:43:59.02343'
  )

优化建议 #

• 给status表建立(id, event_timestamp)联合索引，给区间表user_time_ranges建立(id, start_user_timestamp, end_user_timestamp)联合索引，可大幅降低查询耗时
• 如果需要批量处理多个id的匹配逻辑，使用JOIN代替EXISTS的性能表现更好，可一次性返回所有id的匹配结果
• 注意时间戳的精度差异，避免因毫秒、微秒级的精度差导致边界判断出错，业务不需要高精度时可先统一做精度截断再比较

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