PostgreSQL将时间范围拆分为1分钟时段（不四舍五入边界）

需求说明 #

现有一张PostgreSQL表（假设表名为time_records），包含timestamp类型的start_datetime、end_datetime，以及duration、id列，示例数据如下：

需要将每条记录的时间范围拆分为以分钟结束时刻或原end_datetime为边界的时段，最终得到如下结果：

实现方案 #

以下SQL语句可直接实现该需求，核心思路是生成分钟边界点，结合窗口函数拆分时间区间：

WITH time_boundaries AS (
    SELECT
        id,
        start_datetime,
        end_datetime,
        -- 生成当前记录时间范围内的所有分钟整点边界
        generate_series(
            date_trunc('minute', start_datetime) + interval '1 minute',
            date_trunc('minute', end_datetime),
            interval '1 minute'
        ) AS boundary
    FROM time_records
)
SELECT
    -- 确定分段的起始时间：首段用原start_datetime，后续用前一个边界点
    CASE
        WHEN LAG(tb.boundary) OVER (PARTITION BY tr.id ORDER BY tb.boundary) IS NULL THEN tr.start_datetime
        ELSE LAG(tb.boundary) OVER (PARTITION BY tr.id ORDER BY tb.boundary)
    END AS start_datetime,
    -- 确定分段的结束时间：末段用原end_datetime，其他用当前边界点
    CASE
        WHEN tb.boundary IS NULL THEN tr.end_datetime
        ELSE tb.boundary
    END AS end_datetime,
    tr.id
FROM time_records tr
LEFT JOIN time_boundaries tb ON tr.id = tb.id
-- 补充处理时间范围完全在同一分钟内的记录
UNION ALL
SELECT
    start_datetime,
    end_datetime,
    id
FROM time_records
WHERE date_trunc('minute', start_datetime) = date_trunc('minute', end_datetime)
-- 按id和起始时间排序，保证结果顺序正确
ORDER BY id, start_datetime;

关键逻辑说明 #

1. 生成分钟边界：使用date_trunc('minute', ...)获取时间的分钟整点，再通过generate_series生成时间范围内所有后续的分钟整点，作为拆分的边界点。
2. 拆分时间区间：通过窗口函数LAG获取当前边界的前一个边界，结合原记录的起止时间，确定每个分段的起止点。
3. 边界情况处理：用UNION ALL补充处理那些时间范围不足1分钟的记录，避免遗漏。

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