timestamp with timezone - 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;

question from:https://stackoverflow.com/questions/65895131/easiest-way-to-convert-timestamp-ntz-to-timestamp-tz-in-snowflake-database

Answer 1

You should be able to use the CONVERT_TIMEZONE for this. The 2 argument version Doesn't expect TIMESTAMP_TZ as you mentioned in your comment. You can see below that I'm passing in a TIMESTAMP_NTZ for demonstration purposes but you could pass in a string like 2020-01-27T08:00:00Z too if you want:

select CONVERT_TIMEZONE( 'America/Chicago' , TO_TIMESTAMP_NTZ('2020-01-27T08:00:00Z'));

The above returns

2021-01-26 02:00:00.000000000 -06:00