Converting the timestamp in Snowflake-CodePudding

I'm trying to convert the snowflake timestamp that is compatible with Iterable date format

My attempt:

I have a table with the following timestamps:

|------------------------------|
|       table_timestamp        |
|------------------------------|
| "2021-07--19 02:45:91.000 Z" |
|------------------------------|

I tried using TIMESTAMP_TZ and I got the following resut

SELECT table_timestamp::TIMESTAMP_TZ 
from my_table

gave me output 2021-07--19 02:45:91.000 0000

How can I get the above timestamp in this format 2021-07--19 02:45:91 00:00?

CodePudding user response：

You can cast to a varchar and give, as the second parameter, the format that you want:

SELECT TO_VARCHAR('2021-07-19 02:45:31.000'::Timestamp_TZ, 'yyyy-mm-dd hh:mi:ss')

2021-07-19 02:45:31

(Note I changed the seconds to 31 as there isn't 91 seconds in a minute and also changed your double dash between month and day to a single. I'm assuming those were typos)

CodePudding user response：

Give you are correct converting the date/timestamp, this is not a convertion problem, it is a presentation problem.

Thus the Date and Time output formatting help is what you need.