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?

Tevere

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

Haden

Answer 2

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)

Tevere

Thank you! I've been trying to find a solution that doesn't involve altering the session forever. This sure is ugly, but it works :) – Southbound

Answer 3

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)

Discontented

Answer 4

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).

Pauperize

Answer 5

Thanks to @RubenLaguna for the ugly solution I've used here in mine.

I wanted to be able to convert a datetime_ntz, into a datetime_tz with a timezone of my choosing (my data has a unix time field and the relevant user's tz).

I wrote a UDF to do the convert and ugly tz_from_parts:

create or replace function timestamp_tz_from_ntz(
    "source_timestamp_ntz"  timestamp_ntz,
    "target_tz"             varchar(100),
    "source_tz"             varchar(100)    default 'UTC'
)
returns timestamp_tz
language sql
as
$$
with converted_ntz as (
    select convert_timezone(
        source_tz,
        target_tz,
        source_timestamp_ntz
    ) as d
)
select top 1 
    TIMESTAMP_TZ_FROM_PARTS( 
        year(d), month(d), day(d), hour(d), minute(d), second(d), date_part(nanosecond, source_timestamp_ntz), target_tz 
    ) 
from 
    converted_ntz
$$
;

Some example output:

select 
    '2019-10-24 12:07:24.567'::timestamp_ntz as orig,
    timestamp_tz_from_ntz(orig, 'UTC') as to_utc,
    timestamp_tz_from_ntz(orig, 'Europe/London') as to_london,
    timestamp_tz_from_ntz(orig, 'Europe/Berlin', 'Europe/London') as to_berlin_from_london,
;
---
2019-10-24 12:07:24.567       -- ORIG
2019-10-24 12:07:24.567 +0000 -- TO_UTC
2019-10-24 13:07:24.567 +0100 -- TO_LONDON  
2019-10-24 13:07:24.567 +0200 -- TO_BERLIN_FROM_LONDON

Fracas

Answer 6

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

Permissive

No, that assumes that orig is already in the local timezone which is not. And the conversion would be is totally wrong. You can look at your own output 2019-10-24 19:07:24.000 +0000 that is not the expected result: 2019-10-24 12:07:24 +0000 – Tevere