Snowflake: conversion of timestamp NTZ to UTC with offset
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.