CONVERT_TIMEZONE
The Snowflake data platform’s CONVERT_TIMEZONE function is a convenient tool for converting timestamps from one timezone to another. This capability is crucial for businesses operating across multiple timezones, as it allows them to standardize their data to a single reference point. This guide will walk you through the process of using CONVERT_TIMEZONE, complete with practical examples.
Understanding CONVERT_TIMEZONE
In Snowflake, the syntax of the CONVERT_TIMEZONE function is as follows:
CONVERT_TIMEZONE( <source_tz> , <target_tz> , <source_timestamp_ntz> )
CONVERT_TIMEZONE( <target_tz> , <source_timestamp> )Using CONVERT_TIMEZONE: A Practical Example
Let’s create a table and populate it with sample data to demonstrate the use of the CONVERT_TIMEZONE function. We’ll use the hypothetical ‘freshers_in’ table, which records the joining date and time of new hires in an India-based company.
CREATE TABLE freshers_in
(
fresher_id NUMBER,
name STRING,
join_date_time TIMESTAMP_TZ
);
Add a few rows of data
INSERT INTO freshers_in (fresher_id, name, join_date_time)
VALUES
(1, 'Sachin', '2023-06-12 09:00:00.0 +05:30'),
(2, 'Mahesh', '2023-06-12 12:30:00.0 +05:30'),
(3, 'Raju', '2023-06-12 15:45:00.0 +05:30');
Snowflake uses the IANA (Internet Assigned Numbers Authority) time zone names instead of the common abbreviations like ‘PST’. The IANA time zone name for Pacific Standard Time is ‘America/Los_Angeles’.
SELECT
fresher_id,
join_date_time AS original_timestamp,
CONVERT_TIMEZONE('America/Los_Angeles', join_date_time) AS pst_timestamp,
CONVERT_TIMEZONE('UTC', join_date_time) AS utc_timestamp
FROM freshers_in;Result
FRESHER_ID ORIGINAL_TIMESTAMP PST_TIMESTAMP UTC_TIMESTAMP
1 2023-06-12 09:00:00.000 +0530 2023-06-11 20:30:00.000 -0700 2023-06-12 03:30:00.000 +0000
2 2023-06-12 12:30:00.000 +0530 2023-06-12 00:00:00.000 -0700 2023-06-12 07:00:00.000 +0000
3 2023-06-12 15:45:00.000 +0530 2023-06-12 03:15:00.000 -0700 2023-06-12 10:15:00.000 +0000Snowflake important urls to refer
