Clock Timestamp

Date/Time Function

Description

Return a timestamp equivalent to the current time including time zone.

Click here to navigate to the PostgreSQL docs page for this syntax.

The function clock_timestamp() is translated to CURRENT_TIMESTAMP() in snowflake

Grammar Syntax

CLOCK_TIMESTAMP()

Sample Source Patterns With SELECT Query.

Postgre

Code 1

SELECT clock_timestamp();

Snowflake

Code 1

ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
SELECT
    CURRENT_TIMESTAMP(3);

Sample Source Patterns With CREATE TABLE.

Timestamp

Postgre

Timestamp Without Time Zone

CREATE TABLE table1 (
    dt_update timestamp without time zone DEFAULT clock_timestamp()
    );

Timestamp With Time Zone

CREATE TABLE table1 (
    dt_update timestamp without time zone DEFAULT clock_timestamp()
    );

Snowflake

Timestamp Without Time Zone

CREATE TABLE table1 (
    dt_update TIMESTAMP_NTZ DEFAULT
    /*** MSC-INFORMATION - MSCINF0039 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ
);

Timestamp With Time Zone

CREATE TABLE table1 (
    dt_update TIMESTAMP_TZ DEFAULT
    /*** MSC-INFORMATION - MSCINF0039 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: TIMESTAMP_TZ
);

Date, Time, and Interval

Postgre

Date

CREATE TABLE table1 (
    dt_update date DEFAULT clock_timestamp()
    );

Time Without Time Zone

CREATE TABLE table1 (
    dt_update timestamp without time zone DEFAULT clock_timestamp()
    );

Time With Time Zone

CREATE TABLE table1 (
    dt_update time  with time zone DEFAULT clock_timestamp()
    );

Interval

CREATE TABLE table1 (
    dt_update interval DEFAULT clock_timestamp()
    );

Snowflake

Date

CREATE TABLE table1 (
    dt_update date DEFAULT
    /*** MSC-INFORMATION - MSCINF0039 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: DATE
);

Time Without Time Zone

CREATE TABLE table1 (
    dt_update time
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. without TIME ZONE **
--                    without time zone
                                      DEFAULT
    /*** MSC-INFORMATION - MSCINF0039 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: TIME
);

Time With Time Zone

CREATE TABLE table1 (
    dt_update time
-- ** MSC-ERROR - MSCEWI1096 - TIME ZONE NOT SUPPORTED FOR TIME DATA TYPE **
--                    with time zone
                                   DEFAULT
    /*** MSC-INFORMATION - MSCINF0039 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: TIME
);

Interval

CREATE TABLE table1 (
    dt_update VARCHAR(30) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT
    /*** MSC-INFORMATION - MSCINF0039 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: VARCHAR(30)
);

Observations

• In the case of a select with clock_timestamp, ALTER SESSION is added to maintain compatibility with the PostgreSQL, Greenplum, and Redshift formats. And a precision of 3 is added to the CURRENT_TIMESTAMP for the same reason.

• MSCINF0039 is added because when a SELECT is executed to the table the format may vary.

• The TIMESTAMP_NTZ type is not affected by the ALTER SESSION set timestamp_output_format.

• The ALTER SESSION set timestamp_output_format only affects the TIMESTAMP_LTZ and TIMESTAMP_TZ types when a SELECT statement is executed on the specific table. Their precision, which is set to 9 by default, remains unchanged.

Related EWIs

1. MSCINF0039: The date output format may vary.
2. MSCEWI1002: Removed next statement, not applicable in SnowFlake.
3. MSCEWI1096: TIME ZONE not supported for time data type.
4. MSCEWI1036: Data type converted to another data type.