AT TIME ZONE function - Amazon Redshift
SyntaxArgumentsReturn TypeExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

AT TIME ZONE function

AT TIME ZONE specifies which time zone to use with a TIMESTAMP or TIMESTAMPTZ expression.

Syntax

AT TIME ZONE 'timezone'

Arguments

timezone

The TIMEZONE for 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+00

The 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