Problem

Code attempting to transform Snowflake timestamp_ntz values stored in America/New_York local format into UTC time with offset notation did not account for Daylight Savings Time.

Solution

ALTER SESSION SET TIMEZONE = 'America/New_York';
SELECT
    TO_TIMESTAMP_NTZ('12/15/2023 01:02:03', 'mm/dd/yyyy hh24:mi:ss') as mytimestamp,
    mytimestamp::timestamp_tz as orig,
    TO_VARCHAR(mytimestamp, 'YYYY-MM-DDTHH24:MI:SS.FF3-TZHTZM') as datetime_wrong,
    CONVERT_TIMEZONE('UTC','America/New_York',TO_TIMESTAMP_LTZ(mytimestamp)) AS converted_ts,
    DATEDIFF('hour', converted_ts, mytimestamp) as hourdiff,
    TO_VARCHAR(converted_ts) || '-0' || hourdiff || ':00' as datetime;

The key involves using CONVERT_TIMEZONE() with explicit source and target timezones, which properly handles DST transitions.