PostgreSQL中简化时间范围拆分：拆分整日与首尾剩余时段

问题描述 #

给定如下时间范围数据：

需要将这些时间范围拆分为四类：

• 完整的整日区间
• 整日区间开始前的时分区间
• 整日区间结束后的时分区间
• 原区间在单个整日范围内时，直接保留原区间

拆分后的预期结果如下：

目前已有一个可行但逻辑复杂的实现，现提供更简洁的优化方案。

表结构与测试数据 #

CREATE TABLE IF NOT EXISTS metering_ranges (
    metering_point_id text NOT NULL,
    device_id text NOT NULL,
    start_at timestamp with time zone NOT NULL,
    end_at timestamp with time zone NOT NULL
);

INSERT INTO metering_ranges( metering_point_id, device_id,  start_at, end_at)
VALUES 
    ('meter1', 'device1', '2020-01-02 10:30:00', '2025-01-02 14:00:00'),
    ('meter2', 'device1', '2020-01-02 10:30:00', '2020-01-02 11:30:00'),
    ('meter3', 'device1', '2020-01-02 10:30:00', '2020-01-03 11:30:00');

现有复杂方案 #

with 

ranges_with_whole_days as (
    SELECT
        metering_point_id,
        device_id,
        start_at,
        date_trunc('day', start_at) + interval '1 d' as start_at_next_whole_day,
        date_trunc('day', end_at) as end_at_whole_day,
        end_at
    FROM
        metering_ranges
),

ranges as (
    SELECT
        metering_point_id,
        device_id,
        start_at,
        CASE
            WHEN start_at_next_whole_day <= end_at_whole_day THEN start_at_next_whole_day ELSE NULL
        END as start_at_next_day,
        CASE 
            WHEN end_at_whole_day >= start_at_next_whole_day THEN end_at_whole_day ELSE NULL
        END as end_at_prev_day,
        end_at
    FROM
        ranges_with_whole_days
),

ranges_bucketed AS (
    -- 获取整日区间前的时段
    SELECT metering_point_id, device_id, start_at, start_at_next_day as end_at
    FROM ranges m
    WHERE start_at_next_day IS NOT NULL
    
    UNION
    
    -- 获取整日区间
    SELECT metering_point_id, device_id, start_at_next_day as start_at, end_at_prev_day as end_at
    FROM  ranges m
    WHERE start_at_next_day IS NOT NULL AND end_at_prev_day IS NOT NULL AND start_at_next_day != end_at_prev_day
    
    UNION
    
    -- 获取整日区间后的时段
    SELECT metering_point_id, device_id, end_at_prev_day as start_at, end_at
    FROM ranges m
    WHERE end_at_prev_day IS NOT NULL
    
    UNION
    
    -- 保留单个整日范围内的原区间
    SELECT  metering_point_id,  device_id, start_at, end_at
    FROM ranges m
    WHERE start_at_next_day IS NULL AND end_at_prev_day IS NULL
) 

SELECT *
FROM ranges_bucketed
ORDER BY metering_point_id, device_id, start_at

优化后的简洁方案 #

利用PostgreSQL的generate_series生成整日区间，结合条件判断拆分各部分，逻辑更清晰：

WITH range_parts AS (
    -- 生成起始时段：原start_at到次日0点
    SELECT
        metering_point_id,
        device_id,
        start_at AS start_at,
        date_trunc('day', start_at) + INTERVAL '1 day' AS end_at,
        '起始时段' AS remark
    FROM metering_ranges
    WHERE start_at != date_trunc('day', start_at)
      AND date_trunc('day', start_at) + INTERVAL '1 day' < end_at

    UNION ALL

    -- 生成所有完整整日区间
    SELECT
        metering_point_id,
        device_id,
        day_start AS start_at,
        day_start + INTERVAL '1 day' AS end_at,
        '整日区间' AS remark
    FROM metering_ranges,
         generate_series(
             date_trunc('day', start_at) + INTERVAL '1 day',
             date_trunc('day', end_at) - INTERVAL '1 day',
             INTERVAL '1 day'
         ) AS day_start

    UNION ALL

    -- 生成结束时段：结束日0点到原end_at
    SELECT
        metering_point_id,
        device_id,
        date_trunc('day', end_at) AS start_at,
        end_at AS end_at,
        '结束时段' AS remark
    FROM metering_ranges
    WHERE end_at != date_trunc('day', end_at)
      AND date_trunc('day', end_at) > date_trunc('day', start_at)

    UNION ALL

    -- 保留原区间：当起止时间在同一天时
    SELECT
        metering_point_id,
        device_id,
        start_at,
        end_at,
        '保留原区间' AS remark
    FROM metering_ranges
    WHERE date_trunc('day', start_at) = date_trunc('day', end_at)
)
SELECT *
FROM range_parts
ORDER BY metering_point_id, device_id, start_at;

方案优势 #

1. 逻辑清晰：每个UNION ALL分支对应一类拆分结果，直观易懂各部分作用。
2. 简化整日区间生成：用generate_series自动生成跨多天的所有完整整日区间，无需手动计算范围。
3. 减少中间层：仅用一个CTE完成所有拆分逻辑，避免多层嵌套的复杂条件判断。

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