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> )
Copy

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 is 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).

Returns

Returns a value of type TIMESTAMP_NTZ, TIMESTAMP_TZ, or NULL:

  • For the 3-argument version, returns a value of type TIMESTAMP_NTZ.

  • For the 2-argument version, returns a value of type TIMESTAMP_TZ.

  • If any argument is NULL, returns NULL.

Usage notes

  • The display format for timestamps in the output is determined by the timestamp output format for the current session and the data type of the returned timestamp value.

  • 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 target time zone.

  • For the 2-argument version, the source_timestamp argument typically includes 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.

  • For source_tz and target_tz, you can specify a time zone name or a link name from release 2025b of the IANA Time Zone Database (for example, America/Los_Angeles, Europe/London, UTC, Etc/GMT, and so on).

    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

To use the default timestamp output format for the timestamps returned in the examples, unset the TIMESTAMP_OUTPUT_FORMAT parameter in the current session:

ALTER SESSION UNSET TIMESTAMP_OUTPUT_FORMAT;
Copy

Examples that specify a source time zone

The following examples use the 3-argument version of the CONVERT_TIMEZONE function and specify a source_tz value. These examples return TIMESTAMP_NTZ values.

Convert a “wallclock” time in Los Angeles to the matching “wallclock” time in New York:

SELECT CONVERT_TIMEZONE( 'America/Los_Angeles', 'America/New_York', '2024-01-01 14:00:00'::TIMESTAMP_NTZ ) AS conv;
Copy
+-------------------------+ | CONV | |-------------------------| | 2024-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', '2024-01-01 00:00:00'::TIMESTAMP_NTZ ) AS conv;
Copy
+-------------------------+ | CONV | |-------------------------| | 2023-12-31 23:00:00.000 | +-------------------------+

Examples that do not specify a source time zone

The following examples use the 2-argument version of the CONVERT_TIMEZONE function. These examples return TIMESTAMP_TZ values. Therefore, the returned values include an offset that shows the difference between the timestamp’s time zone and Coordinated Universal Time (UTC). For example, the America/Los_Angeles time zone has an offset of -0700 to show that it is seven hours behind UTC.

Convert a string specifying a TIMESTAMP_TZ value to a different time zone:

SELECT CONVERT_TIMEZONE( 'America/Los_Angeles', '2024-04-05 12:00:00 +02:00' ) AS time_in_la;
Copy
+-------------------------------+ | TIME_IN_LA | |-------------------------------| | 2024-04-05 03:00:00.000 -0700 | +-------------------------------+

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;
Copy
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+ | NOW_IN_LA | NOW_IN_NYC | NOW_IN_PARIS | NOW_IN_TOKYO | |-------------------------------+-------------------------------+-------------------------------+-------------------------------| | 2024-06-12 08:52:53.114 -0700 | 2024-06-12 11:52:53.114 -0400 | 2024-06-12 17:52:53.114 +0200 | 2024-06-13 00:52:53.114 +0900 | +-------------------------------+-------------------------------+-------------------------------+-------------------------------+
Language: English