AT TIME ZONE function - Amazon Redshift
SyntaxArgumentsReturn TypeExamples

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 time zone 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), which is then converted to PST (UTC+8 in POSIX) for display.

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';

  timestamptz 
------------------------
'2001-02-16 19:38:40-08'

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

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

  timestamp
------------------------
'2001-02-16 18:38:40'