TIMESTAMP WITH TIME ZONE Data Type

Description

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The Snowflake equivalent is TIMESTAMP_TZ.(Oracle SQL Language Reference Timestamp with Time Zone Data Type)

The Snowflake equivalent is TIMESTAMP_TZ.

For more information, see also the TIMESTAMP section.

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

Sample Source Patterns

Timestamp with Time Zone in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE timestamp_with_time_zone_table
(
	timestamp_col1 TIMESTAMP(5) WITH TIME ZONE
);


INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE timestamp_with_time_zone_table
	(
		timestamp_col1 TIMESTAMP_TZ(5)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');

Retrieving data from a Timestamp with Time Zone column

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM timestamp_with_time_zone_table;

Result

|TIMESTAMP_COL1               |
|-----------------------------|
|2010-10-10 12:00:00.000 -0600|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
timestamp_with_time_zone_table;

Result

|TIMESTAMP_COL1               |
|-----------------------------|
|2010-10-10 12:00:00.000 -0700|

Note that the timezone is different in both engines because when the timezone is not specified, the default timezone of the database is added.

Use the following syntax to change the default timezone of the database:

ALTER account SET sqtimezone = timezone_string;

Known Issues

1. Timestamp formats may be different

Snow Convert does not perform any conversion for the date/timestamps format strings, so there may be errors when deploying the code. Example:

Oracle

IN -> Oracle_03.sql

INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');

Snowflake

OUT -> Oracle_03.sql

INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');

The query will fail in Snowflake because the default timestamp input format does not recognize '-8:00' as a valid UTC offset. It should be replaced with '-0800' or '-08:00' in order to get the same result.

Related EWIs

No related EWIs.