Formatting timestamp ntz in Snowflow to output as date time YYYY-MM-DDTHH:MMZ

Question

I have TIMESTAMP NTZ field in Snowflake. I need to output the value of the column in the following format:

YYYY-MM-DDTHH:MMZ

I tried the following but it didn't work:

SELECT to_timestamp_tz(MAX("UPDATED_AT"), 'yyyy-mm-ddTHH:MMZ') FROM HISTORY;

Answer 1

If you have a timestamp_ntz field that you want to represent in a specific way then you can use the to_char function like this (assuming you want 24hr time):

SELECT
    to_char(MAX("UPDATED_AT"), 'YYYY-MM-DDTHH24:MIZ')
FROM HISTORY;

Complete example:

create or replace temporary table test_table
(
    id number,
    ts timestamp_ntz
);

insert overwrite into test_table
values (1, '2021-04-13T01:00:00'::timestamp_ntz),
       (2, '2021-04-14T01:00:00'::timestamp_ntz)
;

select
    id,
    to_char(ts, 'YYYY-MM-DDTHH24:MIZ')
from test_table
;

The above produces:

+--+----------------------------------+
|ID|TO_CHAR(TS, 'YYYY-MM-DDTHH24:MIZ')|
+--+----------------------------------+
|1 |2021-04-13T01:00Z                 |
|2 |2021-04-14T01:00Z                 |
+--+----------------------------------+