TIMESTAMP WITH LOCAL TIME ZONE Data Type

Description

It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data..(Oracle SQL Language Reference Timestamp with Local Time Zone Data Type)

The Snowflake equivalent is TIMESTAMP_LTZ.

For more information, see also the TIMESTAMP section.

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

Sample Source Patterns

Timestamp with Time Zone in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE timestamp_with_local_time_zone_table
(
	timestamp_col1 TIMESTAMP(5) WITH LOCAL TIME ZONE
);

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

Snowflake

Query

OUT -> Oracle_01.sql

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

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

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

Retrieving data from a Timestamp with Local Time Zone column

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM timestamp_with_local_time_zone_table;

Result

|TIMESTAMP_COL1         |
|-----------------------|
|2010-10-10 18:00:00.000|
|2010-10-10 20:00:00.000|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
timestamp_with_local_time_zone_table;

Result

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

Note that the results are different in both engines because each database is set with a different time zone. The Oracle timezone is '+00:00' and the Snowflake timezone is 'America/Los_Angeles'.

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

ALTER account SET timezone = timezone_string;

Known Issues

1. Default database timezone

The operations with this kind of data type will be affected by the database timezone, the results may be different. You can check the default timezone using the following queries:

Oracle

Query

IN -> Oracle_03.sql

SELECT dbtimezone FROM dual;

Snowflake

Query

OUT -> Oracle_03.sql

SELECT dbtimezone FROM dual;

2. Oracle Timestamp with local timezone behavior

When operating timestamps with local timezone data types, Oracle converts the timestamps to the default timezone of the database. In order to emulate this behavior in Snowflake, the TIMESTAMP_TYPE_MAPPING session parameter should be set to 'TIMESTAMP_LTZ'.

Query

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';

3. 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

Query

IN -> Oracle_04.sql

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

Snowflake

Query

OUT -> Oracle_04.sql

INSERT INTO timestamp_with_local_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.