在Snowflake中拆分跨00:00的Timestamp_NTZ(9)时间记录

输入示例表 #

先创建并插入测试数据：

CREATE OR REPLACE TABLE input_records (
    id INT,
    StartDate TIMESTAMP_NTZ(9),
    EndDate TIMESTAMP_NTZ(9)
);

INSERT INTO input_records VALUES
(1, '2024-05-20 22:00:00.000', '2024-05-21 02:00:00.000'),
(2, '2024-05-21 09:00:00.000', '2024-05-21 17:00:00.000'),
(3, '2024-05-22 23:00:00.000', '2024-05-24 01:00:00.000');

解决方案SQL #

直接用以下语句实现跨天拆分：

WITH date_series AS (
    SELECT 
        id,
        StartDate,
        EndDate,
        DATEADD(day, seq4(), DATE_TRUNC('day', StartDate)) AS split_date
    FROM input_records,
    TABLE(GENERATOR(ROWCOUNT => (SELECT MAX(DATEDIFF(day, StartDate, EndDate)) + 1 FROM input_records)))
    WHERE split_date <= DATE_TRUNC('day', EndDate)
)
SELECT 
    id,
    CASE 
        WHEN split_date = DATE_TRUNC('day', StartDate) THEN StartDate
        ELSE split_date::TIMESTAMP_NTZ(9)
    END AS split_start,
    CASE 
        WHEN split_date = DATE_TRUNC('day', EndDate) THEN EndDate
        ELSE DATEADD(day, 1, split_date)::TIMESTAMP_NTZ(9)
    END AS split_end
FROM date_series
ORDER BY id, split_start;

关键逻辑说明 #

• 生成日期序列：通过GENERATOR函数生成足够行数，结合DATEADD和DATE_TRUNC，得到原记录起始日期到结束日期的所有日期节点，确保覆盖所有跨天区间。
• 拆分时间段：
  • 每条拆分记录的起始时间：如果是原记录的第一天，用原StartDate；否则取当天00:00。
  • 每条拆分记录的结束时间：如果是原记录的最后一天，用原EndDate；否则取次日00:00。
• 类型兼容：通过类型转换保证输出的split_start和split_end与原字段的TIMESTAMP_NTZ(9)类型一致。

输出结果示例 #

执行后会得到如下符合预期的结果：

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