Easiest way to convert timestamp_ntz to timestamp_tz in Snowflake database

Question

I have a Timestamp_NTZ column where I know the data are in Central (America/Chicago) timezone. How do I convert it into Timestamp_TZ format during select.

I couldn't just convert to varchar and add timezone offset and back to timestamp_tz because of different offset during daylight saving.

I found following two approaches but they involves more code/typing. Looking for elegant solution.

1. Alter session to the required timezone and use TO_TIMESTAMP_TZ() function. The drawback of this approach is that its two separate query and it cannot be used in stored procedure with run as Owner.

   alter session set timezone = 'America/Chicago';
   select TO_TIMESTAMP_TZ(column_name) from table_name;
   

2. Use TIMESTAMP_TZ_FROM_PARTS function. This involves more typing and looks complicated.

   select TIMESTAMP_TZ_FROM_PARTS(Year(column_name), Month(column_name), Day(column_name), Hour(column_name), Minute(column_name), Second(column_name), 0, 'America/Chicago') from table_name;
   

Is there a simple way to do this in snowflake, something like:

select TO_TIMESTAMP_TZ(column_name, 'America/Chicago') from table_name;

Answer 1

All the previous answers require hard-coding time zone or making assumptions about it. Came up with this (or reinvented someone else's) somewhat clean solution to convert UTC timestamp in TIMESTAMP_NTZ format to timezone-aware format.

SELECT (DATE_PART(EPOCH_NANOSECOND, '2024-10-01 05:00'::TIMESTAMP_NTZ)/1000000000.0)::TIMESTAMP_LTZ

Convert to unix epoch number and back. DATE_PART seems to assume UTC timezone in case the input value is naive timestamp.

In case the source timestamp was something else than UTC combining this with CONVERT_TIMEZONE would help.

Answer 2

Based on Ilkka Kudjoi 's answer. I write the following UDF. It adds any time zone to a TIMESTAMP_NTZ, regardless of session timezone. It is similar to SQL server 's "AT TIME ZONE".

CREATE OR replace FUNCTION AT_TIMEZONE(DATETIME_ntz TIMESTAMP_NTZ, time_zone string)
RETURNS TIMESTAMP_TZ
AS $$
    CONVERT_TIMEZONE(time_zone,  (DATE_PART(EPOCH_NANOSECOND, CONVERT_TIMEZONE(time_zone,'UTC',DATETIME_ntz ))/1000000000.0)::TIMESTAMP_LTZ)
$$;

--SELECT AT_TIMEZONE('2024-07-11 14:50:10','America/Chicago')::varchar  --2024-07-11 14:50:10.000 -0500