Snowflake中如何避免时区转换后DateHour列出现重复值？

某事实表以CustomerId与DateHour为数据粒度，其中DateHour为UTC时区。需将其转换为America/Halifax时区用于报表，但夏令时引发两个问题：

• 3月13日会丢失1小时（UTC时间转换后跳过某一本地小时）
• 11月6日会出现同一本地小时重复的情况
  最终导致CustomerId与转换后的DateHour组合不再唯一。示例查询如下：

alter session set timezone = 'UTC';
select '2022-03-13T05:00:00Z'::timestamp as UTC_Time, CONVERT_TIMEZONE('UTC','America/Halifax','2022-03-13T05:00:00Z') as converted_time
union all 
select '2022-03-13T06:00:00Z'::timestamp as UTC_Time, CONVERT_TIMEZONE('UTC','America/Halifax','2022-03-13T06:00:00Z') as converted_time
union all 
select '2022-11-06T04:00:00Z'::timestamp as UTC_Time, CONVERT_TIMEZONE('UTC','America/Halifax','2022-11-06T04:00:00Z') as converted_time
union all 
select '2022-11-06T05:00:00Z'::timestamp as UTC_Time, CONVERT_TIMEZONE('UTC','America/Halifax','2022-11-06T05:00:00Z') as converted_time;

解决方案 #

方法1：使用带时区信息的复合唯一键 #

不要将转换后的时间截断为不带时区的本地小时，而是保留时区偏移信息，用CustomerId + 本地小时 + 时区偏移作为复合唯一键，既保证唯一性，又能准确对应实际时间：

SELECT 
    CustomerId,
    DATE_TRUNC('hour', CONVERT_TIMEZONE('UTC','America/Halifax', DateHour)) AS local_datehour,
    EXTRACT(TIMEZONE_HOUR FROM CONVERT_TIMEZONE('UTC','America/Halifax', DateHour)) AS tz_offset
    -- 其他业务字段
FROM fact_table;

报表展示时可以只显示local_datehour，底层用CustomerId+local_datehour+tz_offset做聚合或关联，避免重复。

方法2：给重复夏令时小时添加标识后缀 #

针对11月6日重复的本地小时，给第二次出现的小时添加区分标识，确保CustomerId+转换后DateHour组合唯一。示例：

WITH converted_data AS (
    SELECT 
        CustomerId,
        DateHour AS utc_hour,
        DATE_TRUNC('hour', CONVERT_TIMEZONE('UTC','America/Halifax', DateHour)) AS local_datehour,
        -- 标记是否为夏令时重复的小时
        CASE 
            WHEN EXTRACT(MONTH FROM utc_hour) = 11 
                 AND EXTRACT(DAY FROM utc_hour) = 6
                 AND EXTRACT(HOUR FROM utc_hour) = 5 THEN '-2'
            ELSE ''
        END AS dup_suffix
    FROM fact_table
)
SELECT 
    CustomerId,
    TO_CHAR(local_datehour, 'YYYY-MM-DD HH24') || dup_suffix AS unique_local_datehour
    -- 其他业务字段
FROM converted_data;

这种方法适合报表需要直观展示本地时间的场景，标识后缀可以在报表说明中解释。

方法3：底层保留UTC粒度，仅展示时转换 #

报表底层仍使用CustomerId+UTC的DateHour作为唯一键，仅在展示时转换为America/Halifax时区时间。这种方法完全规避夏令时问题，是最简便的方案：

SELECT 
    CustomerId,
    DateHour AS utc_datehour,
    -- 仅用于报表展示的本地时间
    CONVERT_TIMEZONE('UTC','America/Halifax', DateHour) AS local_datehour_display
    -- 其他业务字段
FROM fact_table;

方法4：生成连续的本地小时序列 #

如果报表需要连续无跳变的时间序列，可以基于UTC小时的连续性，生成连续的本地小时，忽略夏令时的跳变：

WITH ordered_records AS (
    SELECT 
        CustomerId,
        DateHour,
        CONVERT_TIMEZONE('UTC','America/Halifax', DateHour) AS base_local_time,
        -- 按UTC时间排序生成连续序号
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY DateHour) AS hour_seq
    FROM fact_table
)
SELECT 
    CustomerId,
    -- 基于初始本地时间生成连续小时
    DATEADD(hour, hour_seq - 1, (SELECT MIN(base_local_time) FROM ordered_records WHERE CustomerId = ors.CustomerId)) AS continuous_local_hour
    -- 其他业务字段
FROM ordered_records ors;

注意：这种方法生成的本地小时与实际夏令时时间不完全对应，适合仅需要连续时间维度的报表。

内容的提问来源于stack exchange，提问作者Teja Goud Kandula