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

user_90580 0
Viewed 6140

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?

snowflake-cloud-data-platform sql
5 Answers

There are two ugly hacks that you can use

  • TIMESTAMP_TZ_FROM_PARTS: Construct a new TIMESTAMP_TZ using all the components from the source TIMESTAMP_NTZ and provide the timezone as the last parameter. It's very verbose. See the example below.
  • TO_TIMESTAMP_TZ and CONCAT : Serialize the TIMESTAMP_NTZ as a string / VARCHAR add the timezone string at the end of it and reparse the whole thing as a TIMESTAMP_TZ.
select 
       '2019-10-24 12:07:24.567'::timestamp_ntz as orig
       ,TIMESTAMP_TZ_FROM_PARTS( year(orig), month(orig),day(orig), hour(orig), minute(orig), second(orig) , date_part(nanosecond, orig), 'UTC' ) 
       ,TO_TIMESTAMP_TZ(orig::varchar || ' +0000')
;

--
2019-10-24 12:07:24.567        -- TIMESTAMP_NTZ (UTC)
2019-10-24 12:07:24.567 +0000  -- TIMESTAMP_TZ  (UTC)
2019-10-24 12:07:24.567 +0000  -- TIMESTAMP_TZ  (UTC)
user_905800

This works if you know the local timezone of your account, user, or session. For me, it's 'America/New_York':

SELECT current_timestamp::timestamp_ntz as orig, 
       convert_timezone('UTC',convert_timezone('UTC','America/New_York',orig)::timestamp_tz);

Also, if this is a 1-time conversion, you could run:

ALTER SESSION SET TIMEZONE = 'UTC'

This would temporarily display values as UTC and you could convert them to _tz with:

SELECT convert_timezone('UTC', orig)
user_121789800

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"
;
user_32872500

I would suggest setting the session timezone to UTC first.

ALTER SESSION SET TIMEZONE = 'UTC';

If you can't do that, and you don't know what the session timezone is, you could calculate that as part of your query:

SELECT
    DATEDIFF(minute, CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP)::timestamp_ntz, CURRENT_TIMESTAMP) AS utc_offset,
    '2019-10-24 12:07:24.567'::timestamp_ntz AS orig,
    CONVERT_TIMEZONE('UTC', DATEADD(minute, utc_offset, orig)) as tz_time;

This avoids any string manipulation (which I strongly recommend to avoid).

user_19653430

How about converting to local time zone and using convert_timezone to get the timezone

SELECT '2019-10-24 12:07:24.000'::timestamp_ntz::timestamp_ltz as orig ,convert_timezone('UTC', orig); 0/p= 2019-10-24 19:07:24.000 +0000

user_31244650
Related