寻求PostgreSQL中跨天时间范围重叠查询的更优SQL方案

需要从存储为timestamp without time zone类型的start_time和end_time字段中，筛选出与每日20:00:00-23:00:00时段重叠的记录，包括跨多天的时间区间（例如2025-04-21 13:00:00至2025-04-23 14:00:00）。

以下是两种更简洁高效的优化方案，无需依赖自定义函数：

方案一：纯逻辑条件查询 #

直接通过内置时间函数和运算符构建判断条件，逻辑清晰且性能可靠：

SELECT *
FROM tablename
WHERE 
  end_time > date_trunc('day', start_time) + INTERVAL '20 hours'
  AND start_time < date_trunc('day', end_time) + INTERVAL '23 hours';

逻辑说明 #

该条件等价于时间区间与至少某一天的20:00-23:00存在重叠，自动覆盖所有场景：

• 区间完全落在单日20:00-23:00内
• 区间从单日20:00前开始、到20:00-23:00内结束
• 区间从单日20:00-23:00内开始、到23:00后结束
• 区间跨多天（必然覆盖至少一个完整的20:00-23:00时段）

性能优化 #

如果数据量较大，可创建基于表达式的GIST索引加速查询：

CREATE INDEX IF NOT EXISTS tablename_overlap_2023_idx
ON tablename USING gist (
  tsrange(start_time, end_time, '[]'),
  date_trunc('day', start_time) + INTERVAL '20 hours',
  date_trunc('day', end_time) + INTERVAL '23 hours'
);

方案二：使用内置Timerange类型 #

利用PostgreSQL原生的timerange类型处理时间范围，语法更直观：

SELECT *
FROM tablename
WHERE 
  -- 跨天区间必然覆盖至少一个20:00-23:00时段
  (end_time - start_time) >= INTERVAL '24 hours'
  -- 非跨天区间直接判断时间范围是否重叠
  OR timerange(start_time::time, end_time::time, '[]') && timerange('20:00:00', '23:00:00', '[]');

性能优化 #

针对非跨天区间的查询，可创建timerange字段的GIST索引：

CREATE INDEX IF NOT EXISTS tablename_timerange_idx
ON tablename USING gist (
  timerange(start_time::time, end_time::time, '[]')
);

方案对比 #

• 原方案依赖自定义PL/pgSQL函数，而上述方案使用PostgreSQL原生类型与函数，性能更优且无需维护自定义逻辑
• 方案一无需区分跨天/非跨天场景，逻辑统一，适合大多数业务需求
• 方案二语法简洁直观，对非跨天区间的索引查询效率更高

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