如何合并PostgreSQL中间隔不超过5分钟的时间范围？

可以仅用标准SQL实现该需求，无需自定义聚合函数。核心思路是通过窗口函数标记可合并的范围组，再对每组进行范围合并。

实现代码 #

WITH sorted_entries AS (
    -- 按合同ID和时间范围起始时间排序
    SELECT 
        contract_id,
        range,
        lower(range) AS range_start,
        upper(range) AS range_end
    FROM time_entries
    ORDER BY contract_id, range_start
),
grouped_entries AS (
    -- 标记合并分组：当前范围与前一范围间隔超过5分钟则新建分组
    SELECT 
        contract_id,
        range,
        range_start,
        range_end,
        SUM(
            CASE 
                WHEN lag(range_end) OVER (PARTITION BY contract_id ORDER BY range_start) + INTERVAL '5 minutes' >= range_start
                THEN 0
                ELSE 1
            END
        ) OVER (PARTITION BY contract_id ORDER BY range_start) AS group_id
    FROM sorted_entries
)
-- 按分组合并时间范围
SELECT 
    contract_id,
    tsrange(MIN(range_start), MAX(range_end)) AS merged_range
FROM grouped_entries
GROUP BY contract_id, group_id
ORDER BY contract_id, merged_range;

结果验证 #

执行上述SQL后，会得到期望的合并结果：

逻辑说明 #

1. sorted_entries：将每个合同的时间范围按起始时间排序，确保后续能按顺序判断相邻范围的间隔。
2. grouped_entries：使用lag()窗口函数获取前一个范围的结束时间，判断当前范围的起始时间是否在前一个范围结束后5分钟内。如果是，则归为同一组；否则新建一个分组。通过SUM()累积分组ID，实现连续可合并范围的分组标记。
3. 最终聚合：按合同ID和分组ID聚合，用MIN(range_start)和MAX(range_end)生成合并后的时间范围。

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