PostgreSQL 12：基于起止日期生成填充固定值的时间序列数据

需求说明 #

现有包含ID、带时分秒的起止时间、Value的数据集，需要将每条记录的起止日期范围内的每一天都生成单独一行，对应填充原ID和Value，日期格式保留为YYYY-MM-DD并带引号。

输入数据集（假设表名为your_table） #

CREATE TABLE your_table (
    ID INT,
    "Start" TIMESTAMP,
    "End" TIMESTAMP,
    Value INT
);

INSERT INTO your_table VALUES
(232, '2022-06-08 18:49:00', '2022-11-18 08:06:00', 55),
(456, '2022-10-17 10:24:00', '2022-12-16 12:52:00', 100);

实现SQL语句 #

SELECT
    t.ID,
    quote_literal(gs.date::DATE) AS Date,
    t.Value
FROM your_table t
CROSS JOIN LATERAL generate_series(
    date_trunc('day', t."Start")::DATE,
    date_trunc('day', t."End")::DATE,
    INTERVAL '1 day'
) gs(date)
ORDER BY t.ID, gs.date;

关键逻辑解释 #

• date_trunc('day', t."Start")::DATE：把原带时间的起止字段截断到日期部分，确保从当天0点开始生成序列
• generate_series：PostgreSQL内置函数，生成从起始日期到结束日期的连续日期序列，步长设为1天
• CROSS JOIN LATERAL：将每条原表记录与对应的日期序列关联，为每条记录生成多行日期结果
• quote_literal：给日期值添加引号，匹配你期望的输出格式

输出示例 #

ID      Date           Value
232     "2022-06-08"   55
232     "2022-06-09"   55
...
232     "2022-11-18"   55
456     "2022-10-17"   100
456     "2022-10-18"   100
...
456     "2022-12-16"   100

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