Snowflake夏令时结束时：如何将芝加哥壁钟时间正确转UTC？

方法1：使用带时区的时间戳（TO_TIMESTAMP_TZ）直接解析 #

如果能明确标记重复时间对应的时区偏移，或者可以通过数据序列推断，直接用TO_TIMESTAMP_TZ生成带时区的时间戳再转UTC，就能区分两个重复的1点：

with data as (
    select '2023-11-05 00:00' as ts_str, 'CDT' as tz_offset union all
    select '2023-11-05 01:00' as ts_str, 'CDT' as tz_offset union all 
    select '2023-11-05 01:00' as ts_str, 'CST' as tz_offset union all 
    select '2023-11-05 02:00' as ts_str, 'CST' as tz_offset
)
select
    ts_str,
    tz_offset,
    convert_timezone('UTC', to_timestamp_tz(concat(ts_str, ' ', tz_offset))) as ts_utc
from data;

注：夏令时芝加哥时区为CDT（UTC-5），冬令时为CST（UTC-6），第二个1点对应冬令时，转换后就是UTC 07:00。

方法2：利用数据连续性自动修正转换结果 #

如果数据是按小时连续记录的，可通过窗口函数判断重复转换结果，自动修正：

with data as (
    select to_timestamp_ntz('2023-11-05 00:00', 'yyyy-mm-dd hh24:mi') as ts_local union all
    select to_timestamp_ntz('2023-11-05 01:00', 'yyyy-mm-dd hh24:mi') as ts_local union all 
    select to_timestamp_ntz('2023-11-05 01:00', 'yyyy-mm-dd hh24:mi') as ts_local union all 
    select to_timestamp_ntz('2023-11-05 02:00', 'yyyy-mm-dd hh24:mi') as ts_local
),
converted as (
    select
        ts_local,
        convert_timezone('America/Chicago', 'UTC', ts_local) as ts_utc_initial,
        row_number() over (order by ts_local) as rn
    from data
),
corrected as (
    select
        ts_local,
        case
            -- 当当前转换结果与前一条相同时，说明是夏令时结束后的重复时间，加1小时修正
            when ts_utc_initial = lag(ts_utc_initial) over (order by rn)
            then dateadd(hour, 1, ts_utc_initial)
            else ts_utc_initial
        end as ts_utc_corrected
    from converted
)
select ts_local, ts_utc_corrected from corrected;

这个方法无需额外时区标记，仅通过数据的连续性就能自动修正重复时间的转换结果，得到你期望的第二个1点对应UTC 07:00的结果。

原问题原因 #

convert_timezone搭配to_timestamp_ntz生成的无时区时间戳时，Snowflake默认会优先用夏令时偏移（CDT）解析重复的本地时间，导致两个1点都被转换为UTC 06:00。只有带时区的时间戳（TIMESTAMP_TZ）能明确区分不同偏移下的重复时间，或通过序列推断修正结果。

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