Snowflake Timestamp with Timezone issues

1
votes

I think snowflake formats timestamp with time zone incorrectly when using the documented mask for ISO TIMESTAMP Foramts

alter session set TIMESTAMP_NTZ_OUTPUT_FORMAT ='YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM';
select "last_update" from "sakila1"."actor" limit 1

results in 

2006-02-15"T"12:34:33.000000000+00:00

Note that in the output the 'T' is surrounded by quotes, where as I believe the correct output should be

2006-02-15T12:34:33.000000000+00:00

With no quotes surrounding the 'T'.

Using a mask of YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM seems to produce the correct output, but this conflict with the documentation, and the standard they are following.

1
formattingtimestampsnowflake-cloud-data-platform

1 Answers

1
votes

The documentation does specify that the quotes are optional in the note beneath the format list, and just recommends them for disambiguation. However, I'm seeing the same issue that you are. I'm wondering if that bit of documentation is out of date. Either way, I think you're fine using the T without quotes.

For completeness, I'm also seeing this in TO_TIMESTAMP()

select to_timestamp(current_timestamp::timestamp_ntz::string, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM');

Gives

2020-01-03"T"10:56:55.028000000+00:00