SQL compilation error: Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got TIMESTAMP_LTZ(9) for column CREATE_DT

Question

THE QUESTION: WHAT IS THE PROPER DDL TO USE FOR CREATE TABLE...DEFAULT...CURRENT_TIMESTAMP()

I have a CREATE or REPLACE statement that uses a DEFAULT for the CREATE_DT column- when someone inserts data into the table, the current server date/time should populate the column

CREATE OR REPLACE TABLE "EDW_ADMIN"."ETL_SPROC_LOG" (   
   "ETL_SPROC_LOG_ID" NUMBER IDENTITY NOT NULL, 
   "OBJECT_NAME" VARCHAR2(250 CHAR) NOT NULL, 
   "LOG_ENTRY" VARCHAR2(1000 CHAR) NOT NULL, 
   "DYNAMIC_SQL" VARCHAR2(10000 CHAR) NULL,
   "DURATION" NUMBER NULL,
   "ROWS_AFFECTED" NUMBER NULL,
   "ERROR_CODE" VARCHAR2(200 CHAR) NULL, 
   "ERROR_DESC" VARCHAR2(4000 CHAR) NULL, 
   "CREATE_DT" TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
   "CREATE_USER" VARCHAR2(50) NOT NULL DEFAULT CURRENT_USER()
);

When we retrieve data from the column, we would use the following to change the system date/time to our timezone.

ALTER SESSION SET TIMEZONE = 'AMERICA/NEW_YORK';

When we execute a sample insert statement like this one we get an error:

   INSERT INTO edw_admin.ETL_SPROC_LOG (OBJECT_NAME, LOG_ENTRY) VALUES ('OBJ', 'ENTRY1');

SQL compilation error: Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got TIMESTAMP_LTZ(9) for column CREATE_DT

What is the proper DDL to use for defining the DEFAULT CURRENT_TIMESTAMP()? Do we need to alter the session in the DDL Script prior to the create statement setting the Time Zone to NTZ(9)? I would think Snowflake has multiple servers in different timezones and therefore the System Time would be dependent on where the server is.

The Snowflake Documentation says

Returns the current timestamp for the system. https://docs.snowflake.net/manuals/sql-reference/functions/current_timestamp.html

It has no arguments to control the Timezone that it returns.

This page in the Snowflake Documentation alludes to the use of CONVERT_TIMEZONE( source_tz, target_tz, source_timestamp_ntz), but again if we are different timezones depending on the server the DEFAULT is executing on I'd think this would fail as well.

https://docs.snowflake.net/manuals/sql-reference/functions/convert_timezone.html

Answer 1

THE ANSWER: USE DATA TYPE TIMESTAMP_LTZ(9) INSTEAD OF TIMESTAMP FOR THE DDL FOR THE COLUMN (see below)

In order to have the date/time come back in the format of whatever you have your session in, it has to be stored in the table with a column defined as a Local Time Zone value, then use ALTER SESSION later on to select it. This is how the objects in the Information Schema store Timestamps - with data type TIMESTAMP_LTZ(9).

Here's the final code and results

CREATE OR REPLACE TABLE "EDW_ADMIN"."ETL_SPROC_LOG" (   
   "ETL_SPROC_LOG_ID" NUMBER IDENTITY NOT NULL, 
   "OBJECT_NAME" VARCHAR2(250 CHAR) NOT NULL, 
   "LOG_ENTRY" VARCHAR2(1000 CHAR) NOT NULL, 
   "DYNAMIC_SQL" VARCHAR2(10000 CHAR) NULL,
   "DURATION" NUMBER NULL,
   "ROWS_AFFECTED" NUMBER NULL,
   "ERROR_CODE" VARCHAR2(200 CHAR) NULL, 
   "ERROR_DESC" VARCHAR2(4000 CHAR) NULL, 
   "CREATE_DT" TIMESTAMP_LTZ(9) DEFAULT CURRENT_TIMESTAMP(),
   "CREATE_USER" VARCHAR2(50) NOT NULL DEFAULT CURRENT_USER()
);

-- Unit test the new tables identity column and defaults
INSERT INTO edw_admin.ETL_SPROC_LOG (OBJECT_NAME, LOG_ENTRY) VALUES ('OBJ', 'ENTRY1');

ALTER SESSION SET TIMEZONE = 'America/New_York';
select create_dt from etl_sproc_log;
    2019-11-21 15:04:50.108 -0500

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
select create_dt from etl_sproc_log;
    2019-11-21 12:04:50.108 -0800

ALTER SESSION SET TIMEZONE = 'GMT';
select create_dt from etl_sproc_log;
    2019-11-21 20:04:50.108 +0000

ALTER SESSION UNSET TIMEZONE;
select create_dt from etl_sproc_log;
    2019-11-21 12:04:50.108 -0800


truncate table edw_admin.etl_sproc_log;