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> )

<timestamp> | <timestamptz> AT TIME ZONE <zone>::<type>

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)

Related topics

• timestamp and timestamp with time zone data types