CODEWITHSUNDEEP
snowflake-cloud-data-platform
jay
jay

Reputation: 1463

Adding default timestamp to a table in snowflake

I am trying to add a new column of timestamp type to a table with default values using following code;

ALTER TABLE "DATABASE"."SCHEMA"."TABLE" ADD COLUMN PRESENT_TIME TIMESTAMPNTZ DEFAULT CONVERT_TIMEZONE('UTC',current_timestamp())::TIMESTAMP_NTZ

But this is giving me an error;

SQL compilation error: Invalid column default expression [CAST(CONVERT_TIMEZONE('UTC', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_TZ(9))) AS TIMESTAMP_NTZ(9))]

Edit

ALTER TABLE "DATABASE"."SCHEMA"."TABLE" ADD COLUMN PRESENT_TIME TIMESTAMP
DEFAULT CURRENT_TIMESTAMP()

Error:

Invalid column default expression [CURRENT_TIMESTAMP()]

Can I kindly get help to rectify this error? thanks

Upvotes: 2

Views: 14901

Answers (3)

hgr
hgr

Reputation: 326

Here is a workaround using update statement:

Create the column_name with timestamp datatype

ALTER TABLE table_name ADD column_name TIMESTAMP_TZ(9);

Since the newly create column will be null, you could update it:

UPDATE table_name SET column_name = cast(CURRENT_TIMESTAMP() as TIMESTAMP_TZ(9)) WHERE column_name IS NULL;

Upvotes: 1

Anil Dhakal
Anil Dhakal

Reputation: 114

CREATE OR REPLACE TABLE "DATABASE"."SCHEMA"."TABLE"
   ("ID" INT, "PRESENT_TIME" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP());

Upvotes: 5

Ravikiran Rao
Ravikiran Rao

Reputation: 34

Giving a tip. Might be helpful seeing you struggle to convert time to utc. You can use sysdate() to get utc time.

Upvotes: -1

Related Questions