MSC-PG0004

The date output format may vary

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

The date output format may vary depending on the Timestamp type and the timestamp_output_format being used, you can consult here.

Code Example

Input Code:

PostgreSQL

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

Output Code:

Snowflake

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

Samples

Example with CREATE TABLE.

Input Code:

PostgreSQL

CREATE TABLE sample2 (
    platform_id integer NOT NULL,
    dt_update timestamp with time zone DEFAULT clock_timestamp()
);
    
insert into postgres.public.sample2 (platform_id) values (1);
   
select *, clock_timestamp() from postgres.public.sample2;

Snowflake

CREATE TABLE sample2 (
    platform_id integer NOT NULL,
    dt_update TIMESTAMP_TZ DEFAULT
    /*** MSC-WARNING - MSC-PG0004 - THE DATE OUTPUT FORMAT MAY VARY DEPENDING ON THE TIMESTAMP TYPE AND THE TIMESTAMP_OUTPUT_FORMAT BEING USED. ***/
    CURRENT_TIMESTAMP() :: TIMESTAMP_TZ
);

INSERT INTO sample2 (platform_id) VALUES (1);

ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM';

select *,
    CURRENT_TIMESTAMP(3)
from
    sample2;

Output Code:

PostgreSQL

platform_id|dt_update                    |clock_timestamp              |
-----------+-----------------------------+-----------------------------+
          1|2023-02-05 22:47:34.275 -0600|2023-02-05 23:16:15.754 -0600|

Snowflake

PLATFORM_ID   DT_UPDATE	                        CURRENT_TIMESTAMP(3)
1	      2023-02-05 20:52:30.082000000	2023-02-05 21:20:31.593

Example with SELECT with clock_timestamp().

Input Code

PostgreSQL

select clock_timestamp();

Snowflake

select CURRENT_TIMESTAMP();

Snowflake with transformation

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

Output Code

PostgreSQL

clock_timestamp        |
-----------------------+
2023-02-05 23:24:13.740|

Snowflake

CURRENT_TIMESTAMP()
2023-02-05 21:26:55.211 -0800

Snowflake with transformation

CURRENT_TIMESTAMP(3)
2023-02-05 21:29:24.258

Recommendations

• If you need more support, you can email us at [email protected]