Snowflake : Converting timestamps from one timezone to another[CONVERT_TIMEZONE]

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 +0000

Snowflake important urls to refer

1. Snowflake
2. snowflake_interview
3. Cloud Database
4. cloud_storage