How can I convert a TIMESTAMP_NTZ what I know is in UTC to TIMESTAMP_TZ in Snowflake SQL?

Question

I have a column of type TIMESTAMP_NTZ and it contains timestamp that I know are on UTC timezone. Mainly I want to convert to TIMESTAMP_TZ because I have other columns that are TIMESTAMP_TZ and I want to be able to do operations involving both.

I've tried to generate a TIMESTAMP_TZ in the following ways:

SELECT 
   '2019-10-24 12:07:24.000'::timestamp_ntz as orig -- a literal timestamp_ntz 
   ,convert_timezone('UTC', 'UTC', orig) -- the 3-args version of convert_timezone will always return a TIMESTAMP_NTZ
   ,convert_timezone('UTC', orig) -- the 2-args assumes that orig timezones is session's current timezone 
;

Just to be clear I want to convert 2019-10-24 12:07:24.000 to 2019-10-24 12:07:24.000 +0000

Is there any function or operator in Snowflake that allows me to say treat this TIMESTAMP_NTZ as TIMESTAMP_TZ in timezone X?

Answer 1

You should be able to just suffix a "Z" onto the end of the original TIMESTAMP_NTZ and then convert it to a TIMESTAMP_TZ like the following:

-- Set timezone to something which isn't UTC 
alter session set TIMEZONE = 'America/Los_Angeles';
-- Make sure the timestamp TZ output format is as you'd expect
alter session set TIMESTAMP_TZ_OUTPUT_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM";

select 
       '2019-10-24 12:07:24.567'::timestamp_ntz as orig, -- Outputs "2019-10-24 12:07:24.567000000"
       (orig::varchar||'Z')::timestamp_tz timestamp_with_tz --Outputs "2019-10-24 12:07:24.567 +0000"
;