Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CONVERT_TIMEZONE Function

CONVERT_TIMEZONE converts a time stamp from one time zone to another.

Syntax

Copy
CONVERT_TIMEZONE ( ['source_timezone',] 'target_timezone', 'timestamp')

Arguments

source_timezone

(Optional) The time zone of the current time stamp. The default is UTC. For more information, see Time Zone Usage Notes.

target_timezone

The time zone for the new time stamp. For more information, see Time Zone Usage Notes.

timestamp

A timestamp column or an expression that implicitly converts to a time stamp.

Return Type

TIMESTAMP

Time Zone Usage Notes

Either source_timezone or target_timezone 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, execute the following command.

Copy
select pg_timezone_names();
To view a list of supported time zone abbreviations, execute the following command.
Copy
select pg_timezone_abbrevs();

Using a Time Zone Name

If you specify a time zone using a time zone name, CONVERT_TIMEZONE automatically adjusts for Daylight Saving Time (DST), or any other local seasonal protocol, such as Summer Time, Standard Time, or Winter Time, that is in force for that time zone during the date and time specified by 'timestamp'. For example, 'Europe/London' represents UTC in the winter and UTC+1 in the summer.

Using a Time Zone Abbreviation

Time zone abbreviations represent a fixed offset from UTC. If you specify a time zone using a time zone abbreviation, CONVERT_TIMEZONE uses the fixed offset from UTC and does not adjust for any local seasonal protocol. For example, ADT (Atlantic Daylight Time) always represents UTC-03, even in winter.

You can specify an offset from UTC for either source_zone or target_zone by using the format ‘name+offset’, where name is any string of three or more alphabetic characters, and offset is a time value, using the format hh:mm:ss. The offset can be positive or negative.

CONVERT_TIMEZONE interprets the offset as the time from UTC. For example, an offset of +2 is equivalent to UTC–2, and an offset of -2 is equivalent to UTC+2. CONVERT_TIMEZONE does not use the prefix string when calculating the offset, even if the string represents a valid time zone. For example, 'NEWZONE+2’, 'PDT+2', and 'GMT+2' all have the same result. If a string does not include an offset, then it must represent a valid time zone or CONVERT_TIMEZONE returns an error.

Examples

The following example converts the time stamp value in the LISTTIME column from the default UTC time zone to PST. Even though the time stamp is within the daylight time period, it is converted to standard time because the target time zone is specified as an abbreviation (PST).

Copy
select listtime, convert_timezone('PST', listtime) from listing where listid = 16; listtime | convert_timezone --------------------+------------------- 2008-08-24 09:36:12 2008-08-24 01:36:12

The following example converts a timestamp LISTTIME column from the default UTC time zone to US/Pacific time zone. The target time zone uses a time zone name, and the time stamp is within the daylight time period, so the function returns the daylight time.

Copy
select listtime, convert_timezone('US/Pacific', listtime) from listing where listid = 16; listtime | convert_timezone --------------------+--------------------- 2008-08-24 09:36:12 | 2008-08-24 02:36:12

The following example converts a time stamp string from EST to PST:

Copy
select convert_timezone('EST', 'PST', '20080305 12:25:29'); convert_timezone ------------------- 2008-03-05 09:25:29

The following example converts a time stamp to US Eastern Standard Time because the target time zone uses a time zone name (America/New_York) and the time stamp is within the standard time period.

Copy
select convert_timezone('America/New_York', '2013-02-01 08:00:00'); convert_timezone --------------------- 2013-02-01 03:00:00 (1 row)

The following example converts the time stamp to US Eastern Daylight Time because the target time zone uses a time zone name (America/New_York) and the time stamp is within the daylight time period.

Copy
select convert_timezone('America/New_York', '2013-06-01 08:00:00'); convert_timezone --------------------- 2013-06-01 04:00:00 (1 row)

The following example demonstrates the use of offsets.

Copy
SELECT CONVERT_TIMEZONE('GMT','NEWZONE +2','2014-05-17 12:00:00') as newzone_plus_2, CONVERT_TIMEZONE('GMT','NEWZONE-2:15','2014-05-17 12:00:00') as newzone_minus_2_15, CONVERT_TIMEZONE('GMT','America/Los_Angeles+2','2014-05-17 12:00:00') as la_plus_2, CONVERT_TIMEZONE('GMT','GMT+2','2014-05-17 12:00:00') as gmt_plus_2; newzone_plus_2 | newzone_minus_2_15 | la_plus_2 | gmt_plus_2 ---------------------+---------------------+---------------------+--------------------- 2014-05-17 10:00:00 | 2014-05-17 14:15:00 | 2014-05-17 10:00:00 | 2014-05-17 10:00:00 (1 row)