Change output format of current_timestamp in Snowflake

user_16024709 0
Viewed 294

I am trying to update timestamp of certain records in snowflake. I want the timestamp to be stored in specific format (YYYY-MM-DD HH:MM:SS eg - 2021-12-10 10:12:35) in snowflake. I am doing this through R.

I can do this directly in snowflake but I am not able to do this using R.

In snowflake, I run these two lines to get my data in required format

alter session set timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS'
UPDATE table SET TIMESTAMP=current_timestamp

In R, I am using our internal package snowflake to query the data where I do -

snowflake::query("alter session set timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS'")
snowflake::query("UPDATE table SET TIMESTAMP=current_timestamp")

This returns 2021-12-10 10:12:35.349 Z as time which I think is default format to show time in snowflake. So the 1st alter session command has no impact (?).

How can I achieve my expected output using R ?

I think I saw somewhere that I can do current_timestamp.format(...) or something similar to change the format of current_timestamp but I can't find it now.

snowflake-cloud-data-platform r datetime
1 Answers

Dates and timestamps are stored in databases as numbers, there is no way to define a format that they are stored in - as that would make no sense with a number.

The format that a date/timestamp is displayed in is set by the application being used to query the data

user_136583990
Related