PostgreSQL v13如何判断指定时间戳是否包含在一组区间内

核心实现逻辑 #

不需要循环拼接多个BETWEEN判断，PostgreSQL 原生的时间范围类型自带包含判断运算符，配合关联子查询即可单条SQL实现需求。
PostgreSQL 中tstzrange（带时区时间范围类型）的包含判断运算符为@>，语义是判断运算符左侧的值/范围，是否被右侧的范围完整包含，正好匹配「时间戳在某个时间区间内」的判断逻辑。

方案1：EXISTS 子查询写法（性能最优，推荐） #

这个写法和预期语义完全一致，且EXISTS只要匹配到1个符合条件的区间就会终止子查询扫描，不会出现重复返回记录的问题：

SELECT * FROM rides r
WHERE device_id = 1 
AND EXISTS (
    SELECT 1
    FROM segments s
    WHERE s.device_id = r.device_id
      -- 判断当前ride的时间戳是否落在segment的时间区间内
      AND tstzrange(s.start_at, s.end_at) @> r.timestamp
);

这个写法的效果完全等价于手动拼接N个OR timestamp BETWEEN start_n AND end_n的逻辑，不需要应用层循环拼接SQL。

方案2：JOIN 写法（需同时获取匹配分段信息时使用） #

如果需要在查询ride的同时拿到命中的segment相关字段，可以用JOIN写法，注意加DISTINCT去重，避免一个ride匹配多个segment时重复返回同一条ride记录：

SELECT DISTINCT r.*, s.segment_id, s.start_at as seg_start, s.end_at as seg_end
FROM rides r
JOIN segments s 
  ON s.device_id = r.device_id
 AND tstzrange(s.start_at, s.end_at) @> r.timestamp
WHERE r.device_id = 1;

性能优化建议 #

• 当segments表数据量较大时，可以给时间范围建GiST索引，大幅提升范围包含判断的查询速度：

CREATE INDEX idx_segments_time_range ON segments USING GIST (tstzrange(start_at, end_at));

• 如果绝大多数查询都会带上device_id过滤条件，可以建包含设备ID的联合GiST索引，性能会更好：

CREATE INDEX idx_segments_device_timerange ON segments USING GIST (device_id, tstzrange(start_at, end_at));

原写法不生效的原因 #

示例SQL中用了IN运算符，这个运算符的语义是判断左侧值是否和右侧结果集中的单个值完全相等，而tstzrange是范围类型，和timestamp时间戳类型不可能相等，因此永远无法命中条件，范围类型的包含/相交/重叠判断必须使用PostgreSQL提供的专用范围运算符。

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