Materialize Documentation
Join the Community github/materialize

TIMEZONE and AT TIME ZONE functions

TIMEZONE and AT TIME ZONE convert a timestamp or a timestamptz to a different time zone.

Known limitation: You must explicitly cast the type for the time zone.

Signatures

TIMEZONE ( zone :: type , timestamp timestamptz )
SELECT TIMESTAMP TIMESTAMPTZ timestamp timestamptz AT TIME ZONE zone::type
Parameter Type Description
zone text The target time zone.
type text or numeric The datatype in which the time zone is expressed
timestamp timestamp The timestamp without time zone.
timestamptz timestamptz The timestamp with time zone.

Return values

TIMEZONE and AT TIME ZONE return timestamp if the input is timestamptz, and timestamptz if the input is timestamp.

Note: timestamp and timestamptz always store data in UTC, even if the date is returned as the local time.

Examples

Convert timestamp to another time zone, returned as UTC with offset

SELECT TIMESTAMP '2020-12-21 18:53:49' AT TIME ZONE 'America/New_York'::text;
        timezone
------------------------
2020-12-21 23:53:49+00
(1 row)
SELECT TIMEZONE('America/New_York'::text,'2020-12-21 18:53:49');
        timezone
------------------------
2020-12-21 23:53:49+00
(1 row)

Convert timestamp to another time zone, returned as specified local time

SELECT TIMESTAMPTZ '2020-12-21 18:53:49+08' AT TIME ZONE 'America/New_York'::text;
        timezone
------------------------
2020-12-21 05:53:49
(1 row)
SELECT TIMEZONE ('America/New_York'::text,'2020-12-21 18:53:49+08');
        timezone
------------------------
2020-12-21 05:53:49
(1 row)