AT TIME ZONE specifies which time zone to use with a TIMESTAMP or TIMESTAMPTZ expression.
Syntax
AT TIME ZONE 'timezone'
Arguments
- timezone
-
The
TIMEZONEfor the return value. The time zone can be specified as a time zone name (such as'Africa/Kampala'or'Singapore') or as a time zone abbreviation (such as'UTC'or'PDT').To view a list of supported time zone names, run the following command.
select pg_timezone_names();To view a list of supported time zone abbreviations, run the following command.
select pg_timezone_abbrevs();For more information and examples, see Time zone usage notes.
Return type
TIMESTAMPTZ when used with a TIMESTAMP expression. TIMESTAMP when used with a TIMESTAMPTZ expression.
Examples
The following example converts a timestamp value without time zone and interprets it as MST time (UTC+7 in POSIX). The example returns a value of data type TIMESTAMPTZ for the UTC timezone. If you configure your default timezone to a timezone other than UTC, you might see a different result.
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
timezone
------------------------
2001-02-17 03:38:40+00The following example takes an input timestamp with a time zone value where the specified time zone is EST (UTC+5 in POSIX) and converts it to MST (UTC+7 in POSIX). The example returns a value of data type TIMESTAMP.
SELECT TIMESTAMPTZ '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
timezone
------------------------
2001-02-16 18:38:40