Categories:

Date & Time Functions

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.

• Snowflake supports standard iana.org time zones: America/Los_Angeles, Europe/London, UTC, Etc/GMT, etc. However, certain simple time zones, such as PDT, are not currently supported.

  For a list of time zones, see tz database time zones (in Wikipedia).

• Time zone names are case-sensitive and must be enclosed in single quotes.

Examples

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

-- Convert a "wallclock" time in Los Angelese 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 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+