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

{{% include-syntax file="examples/sql_functions/timezone" example="syntax-timezone" %}}

{{% include-syntax file="examples/sql_functions/timezone" example="syntax-at-time-zone" %}}

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.

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.

mzsql

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)

mzsql

SELECT TIMEZONE('America/New_York'::text,'2020-12-21 18:53:49');

        timezone
------------------------
2020-12-21 23:53:49+00
(1 row)

mzsql

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)

mzsql

SELECT TIMEZONE ('America/New_York'::text,'2020-12-21 18:53:49+08');

        timezone
------------------------
2020-12-21 05:53:49
(1 row)