Redshift Time Zone Conversion Tips for Data Analytics

Redshift practical knowledge to speed up analytics process

Photo by noor Younis on Unsplash

Time zone is commonly used in Business Intelligence analytics to allow reports to convert different time zones. In this article, I will share about how Redshift handle the time zone conversions.

Get a list of time zone names and utc_offset:

Redshift provides a built-in timezone table with worldwide timezone.

SELECT tz.* FROM pg_timezone_names() tz(name text, abbrev text, utc_offset interval, is_dst boolean);

Convert time zone to another:

Below illustrated the timezone conversion from UTC to SGT and vice versa with/without timezone specified in the data column.

SELECT DISTINCT
registered_at AS default_tz, -- default timestamp display in DBeaver
registered_at AT TIME ZONE 'UTC' AS utc_tz, -- to prove the default timezone is UTC
registered_at::timestamptz AS default_with_tz, -- to prove the default timestamp with timezone UTC
registered_at::timestamp AS default_no_tz, -- to check the default timestamp without timezone is the same as with timezone
registered_at::timestamp…