PostgreSQL如何按小时间隔拆分跨小时的任务时间记录行

实现思路 #

利用PostgreSQL内置的GENERATE_SERIES函数生成连续小时序列，逐行匹配原表的时间区间完成拆分，再单独计算每个拆分后子区间的起止时间和时长。

完整实现SQL #

SELECT
    job_id,
    -- 计算拆分后行的开始时间：首小时用原开始时间，其余用当前小时整点
    CASE
        WHEN h = start_hour THEN start_datetime
        ELSE date_trunc('day', start_datetime) + INTERVAL '1 hour' * h
    END AS start_datetime,
    -- 计算拆分后行的结束时间：末小时用原结束时间，其余用下一小时整点
    CASE
        WHEN h = end_hour THEN end_datetime
        ELSE date_trunc('day', start_datetime) + INTERVAL '1 hour' * (h + 1)
    END AS end_datetime,
    h AS start_hour,
    h AS end_hour,
    -- 计算秒级时间差
    EXTRACT(EPOCH FROM (
        CASE
            WHEN h = end_hour THEN end_datetime
            ELSE date_trunc('day', start_datetime) + INTERVAL '1 hour' * (h + 1)
        END - 
        CASE
            WHEN h = start_hour THEN start_datetime
            ELSE date_trunc('day', start_datetime) + INTERVAL '1 hour' * h
        END
    ))::INT AS duration
FROM
    table_a,
    -- 生成当前行覆盖的所有小时序列，自动匹配拆分行数
    GENERATE_SERIES(start_hour, end_hour) AS h
ORDER BY
    job_id,
    start_datetime;

逻辑说明 #

• 当原记录start_hour与end_hour相等时，GENERATE_SERIES仅生成1行，完全保留原记录逻辑，无需额外过滤
• 跨小时的记录会按小时数自动拆分，比如start_hour=1、end_hour=5的记录会生成5行，和你给出的job_id=6214550的示例完全匹配
• 由于限定所有时间不跨天，date_trunc('day', start_datetime)取当日零点的逻辑不会出现边界问题
• 如需持久化拆分结果，直接在SQL开头加上CREATE TABLE 拆分后表名 AS即可

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