- Categories:
CONVERT_TIMEZONE¶
Converts a timestamp to another time zone.
Syntax¶
CONVERT_TIMEZONE( <source_tz> , <target_tz> , <source_timestamp_ntz> )
CONVERT_TIMEZONE( <target_tz> , <source_timestamp> )
Arguments¶
source_tz
String specifying the time zone for the input timestamp. Required for timestamps with no time zone (i.e. TIMESTAMP_NTZ).
target_tz
String specifying the time zone to which the input timestamp should be converted.
source_timestamp_ntz
For the 3-argument version, string specifying the timestamp to convert (must be TIMESTAMP_NTZ).
source_timestamp
For the 2-argument version, string specifying the timestamp to convert (can be any timestamp variant, including TIMESTAMP_NTZ).
Usage Notes¶
For the 3-argument version:
The “wallclock” time in the result represents the same moment in time as the input “wallclock” in the input time zone, but in the destination time zone.
The return value is always of type TIMESTAMP_NTZ.
For the 2-argument version:
The
source_timestamp
argument is considered to include the time zone. If the value is of type TIMESTAMP_TZ, the time zone is taken from its value. Otherwise, the current session time zone is used.The return value is always of type TIMESTAMP_TZ.
For
source_tz
andtarget_tz
, you can specify a time zone name or a link name from release 2021a of the IANA Time Zone Database (e.g.America/Los_Angeles
,Europe/London
,UTC
,Etc/GMT
, etc.).Note
Time zone names are case-sensitive and must be enclosed in single quotes (e.g.
'UTC'
).Snowflake does not support the majority of timezone abbreviations (e.g.
PDT
,EST
, etc.) because a given abbreviation might refer to one of several different time zones. For example,CST
might refer to Central Standard Time in North America (UTC-6), Cuba Standard Time (UTC-5), and China Standard Time (UTC+8).
Examples¶
ALTER SESSION SET timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS';
-- Convert a "wallclock" time in Los Angeles to the matching "wallclock" time in New York
SELECT CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', '2019-01-01 14:00:00'::timestamp_ntz) AS conv;
+-------------------------+
| CONV |
|-------------------------|
| 2019-01-01 17:00:00.000 |
+-------------------------+
-- Convert a "wallclock" time in Warsaw to the matching "wallclock" time in UTC
SELECT CONVERT_TIMEZONE('Europe/Warsaw', 'UTC', '2019-01-01 00:00:00'::timestamp_ntz) AS conv;
+-------------------------+
| CONV |
|-------------------------|
| 2018-12-31 23:00:00.000 |
+-------------------------+
ALTER SESSION UNSET timestamp_output_format;
-- Convert TIMESTAMP_TZ to a different time zone and include the time zone in the result
SELECT CONVERT_TIMEZONE('America/Los_Angeles', '2018-04-05 12:00:00 +02:00') AS time_in_la;
+-------------------------------+
| TIME_IN_LA |
|-------------------------------|
| 2018-04-05 03:00:00.000 -0700 |
+-------------------------------+
ALTER SESSION UNSET timestamp_output_format;
-- Show the current "wallclock" time in different time zones
SELECT
CURRENT_TIMESTAMP() AS now_in_la,
CONVERT_TIMEZONE('America/New_York', CURRENT_TIMESTAMP()) AS now_in_nyc,
CONVERT_TIMEZONE('Europe/Paris', CURRENT_TIMESTAMP()) AS now_in_paris,
CONVERT_TIMEZONE('Asia/Tokyo', CURRENT_TIMESTAMP()) AS now_in_tokyo;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| NOW_IN_LA | NOW_IN_NYC | NOW_IN_PARIS | NOW_IN_TOKYO |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2019-01-11 14:23:08.497 -0800 | 2019-01-11 17:23:08.497 -0500 | 2019-01-11 23:23:08.497 +0100 | 2019-01-12 07:23:08.497 +0900 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+