如何在PostgreSQL中查询与指定整数时间区间重叠的数据？

假设你的表名为time_intervals，表结构如下：

CREATE TABLE time_intervals (
    from_secs INT,
    to_secs INT,
    value INT
);

示例数据：

INSERT INTO time_intervals VALUES
(10, 20, 1),
(12, 50, 2);

需求：给定一个区间列表（如[(1, 11), (55, 100)]），找出表中区间(from_secs, to_secs)与列表中至少一个区间重叠的所有value值。

解决方案 #

方法1：用CTE构建输入区间临时表，通过JOIN匹配

WITH input_intervals AS (
    SELECT unnest(ARRAY[(1,11), (55,100)]::INT[][]) AS interval_pair
)
SELECT DISTINCT ti.value
FROM time_intervals ti
JOIN input_intervals ii
ON ti.from_secs < (ii.interval_pair)[2] 
AND ti.to_secs > (ii.interval_pair)[1];

方法2：用EXISTS子查询直接匹配

SELECT DISTINCT value
FROM time_intervals
WHERE EXISTS (
    SELECT 1
    FROM unnest(ARRAY[(1,11), (55,100)]::INT[][]) AS input_intv
    WHERE from_secs < input_intv[2] 
      AND to_secs > input_intv[1]
);

关键说明 #

• 区间重叠判断逻辑：A.from_secs < B.to_secs AND A.to_secs > B.from_secs，该条件能覆盖所有区间有交集的场景（包括一个区间完全包含另一个的情况）。
• DISTINCT用于避免同一value对应多行匹配多个输入区间时重复返回结果。
• 实际使用时替换ARRAY[(1,11), (55,100)]为你的目标区间列表即可。

示例结果 #

对于输入区间列表[(1, 11), (55, 100)]，上述查询会返回：

value
-------
     1

因为第一行的区间(10,20)与(1,11)重叠，而第二行的(12,50)与两个输入区间均无交集。

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