Snowflake
Convert Time Zone

Snowflake CONVERT_TIMEZONE

What it does

CONVERT_TIMEZONE takes a timestamp recorded in one time zone and converts it to the equivalent time in another time zone. It changes the time displayed on a timestamp from one time zone to another, helping you understand what time an event occurred in different parts of the world.

Syntax

You can use CONVERT_TIMEZONE in two ways:

  1. To convert a timestamp from one known time zone to another:

_10
CONVERT_TIMEZONE('<source_timezone>', '<target_timezone>', '<timestamp>')

where:

  • <source_timezone>: The original time zone of the timestamp.
  • <target_timezone>: The time zone you want to convert the timestamp to.
  • <timestamp>: The original timestamp you want to convert.
  1. To convert a timestamp to a target time zone when the timestamp already includes a time zone:

_10
CONVERT_TIMEZONE('<target_timezone>', '<timestamp_with_timezone>')

where:

  • <target_timezone>: The time zone you want to convert the timestamp to.
  • <timestamp_with_timezone>: The timestamp that includes a time zone.

Examples

Converting timestamps to UTC

Imagine a table called international_flights that tracks flight details, including each flight's departure time and the airport's local timezone. We want to convert these local times to UTC to simplify coordination and scheduling at a central operations center.

Input

Table: international_flights

flight_idflight_numberdeparture_airportdeparture_timezonelocal_departure_time
1UA101Los Angeles International AirportAmerica/Los_Angeles2023-04-01 15:00:00.000
2LH456Frankfurt AirportEurope/Berlin2023-04-01 22:00:00.000
3QF33Sydney AirportAustralia/Sydney2023-04-02 11:00:00.000

Snowflake SQL Query


_10
SELECT
_10
flight_id,
_10
flight_number,
_10
departure_airport,
_10
CONVERT_TIMEZONE (departure_timezone, 'UTC', local_departure_time) AS utc_departure_time,
_10
local_departure_time
_10
FROM
_10
international_flights;

Output

flight_idflight_numberdeparture_airportutc_departure_timelocal_departure_time
1UA101Los Angeles International Airport2023-04-01 22:00:00.0002023-04-01 15:00:00.000
2LH456Frankfurt Airport2023-04-01 20:00:00.0002023-04-01 22:00:00.000
3QF33Sydney Airport2023-04-02 01:00:00.0002023-04-02 11:00:00.000

Converting from one time zone to another

To assist international traders, you might want to convert the New York-based exchange times and provide the exchange rates in their local times for key financial centers like London and Tokyo:

Input

Table: currency_exchanges

exchange_idcurrency_pairexchange_rateny_time
1USD/EUR0.902023-04-01 09:00:00.000
2GBP/USD1.352023-04-01 13:00:00.000
3AUD/USD0.752023-04-01 16:00:00.000

Snowflake SQL Query


_10
SELECT
_10
exchange_id,
_10
currency_pair,
_10
exchange_rate,
_10
ny_time,
_10
CONVERT_TIMEZONE ('America/New_York', 'Europe/London', ny_time) AS london_time,
_10
CONVERT_TIMEZONE ('America/New_York', 'Asia/Tokyo', ny_time) AS tokyo_time
_10
FROM
_10
currency_exchanges;

Output

exchange_idcurrency_pairexchange_rateny_timelondon_timetokyo_time
1USD/EUR0.902023-04-01 09:00:00.0002023-04-01 14:00:00.0002023-04-01 22:00:00.000
2GBP/USD1.352023-04-01 13:00:00.0002023-04-01 18:00:00.0002023-04-02 02:00:00.000
3AUD/USD0.752023-04-01 16:00:00.0002023-04-01 21:00:00.0002023-04-02 05:00:00.000
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started