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:
- To convert a timestamp from one known time zone to another:
_10CONVERT_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.
- To convert a timestamp to a target time zone when the timestamp already includes a time zone:
_10CONVERT_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_id | flight_number | departure_airport | departure_timezone | local_departure_time |
|---|---|---|---|---|
| 1 | UA101 | Los Angeles International Airport | America/Los_Angeles | 2023-04-01 15:00:00.000 |
| 2 | LH456 | Frankfurt Airport | Europe/Berlin | 2023-04-01 22:00:00.000 |
| 3 | QF33 | Sydney Airport | Australia/Sydney | 2023-04-02 11:00:00.000 |
Snowflake SQL Query
_10SELECT_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_10FROM_10 international_flights;
Output
| flight_id | flight_number | departure_airport | utc_departure_time | local_departure_time |
|---|---|---|---|---|
| 1 | UA101 | Los Angeles International Airport | 2023-04-01 22:00:00.000 | 2023-04-01 15:00:00.000 |
| 2 | LH456 | Frankfurt Airport | 2023-04-01 20:00:00.000 | 2023-04-01 22:00:00.000 |
| 3 | QF33 | Sydney Airport | 2023-04-02 01:00:00.000 | 2023-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_id | currency_pair | exchange_rate | ny_time |
|---|---|---|---|
| 1 | USD/EUR | 0.90 | 2023-04-01 09:00:00.000 |
| 2 | GBP/USD | 1.35 | 2023-04-01 13:00:00.000 |
| 3 | AUD/USD | 0.75 | 2023-04-01 16:00:00.000 |
Snowflake SQL Query
_10SELECT_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_10FROM_10 currency_exchanges;
Output
| exchange_id | currency_pair | exchange_rate | ny_time | london_time | tokyo_time |
|---|---|---|---|---|---|
| 1 | USD/EUR | 0.90 | 2023-04-01 09:00:00.000 | 2023-04-01 14:00:00.000 | 2023-04-01 22:00:00.000 |
| 2 | GBP/USD | 1.35 | 2023-04-01 13:00:00.000 | 2023-04-01 18:00:00.000 | 2023-04-02 02:00:00.000 |
| 3 | AUD/USD | 0.75 | 2023-04-01 16:00:00.000 | 2023-04-01 21:00:00.000 | 2023-04-02 05:00:00.000 |
