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
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
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
SELECT * FROM timestamp_with_time_zone_table;
Snowflake
SELECT * FROM
timestamp_with_time_zone_table;
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
INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');
Snowflake
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.
Last updated