timezone-and-at-time-zone.md 2.3 KB
title: "TIMEZONE and AT TIME ZONE functions" description: "Converts timestamp to a different time zone." menu: main:
parent: 'sql-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
{{< diagram "func-timezone.svg" >}}
{{< diagram "func-at-time-zone.svg" >}}
| 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)