CONVERT_TIMEZONE function

CONVERT_TIMEZONE converts a timestamp from one time zone to another. The function automatically adjusts for daylight saving time.

Syntax

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

Arguments

Return type

TIMESTAMP

Time zone usage notes

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'). You don't have to convert time zone names to names or abbreviations to abbreviations. For example, you can choose a timestamp from the source time zone name 'Singapore' and convert it to a timestamp in the time zone abbreviation 'PDT'.

Using a time zone name

To view a current and complete list of time zone names, run the following command.

select pg_timezone_names();

Each row contains a comma-separated string with the time zone name, abbreviation, UTC offset, and indicator if the time zone observes daylight-savings (t or f). For example, the following snippet shows two resulting rows. The first row is the time zone Europe/Paris, abbreviation CET, with 01:00:00 offset from UTC, and f to indicate it doesn't observe daylight-savings time. The second row is the time zone Israel, abbreviation IST, with 02:00:00 offset from UTC, and f to indicate it doesn't observe daylight-savings time.

pg_timezone_names
-----------------
(Europe/Paris,CET,01:00:00,f)	
(Israel,IST,02:00:00,f)	

Run the SQL statement to obtain the entire list and find a time zone name. Approximately 600 rows are returned. Even though some of the returned time zone names are capitalized initialisms or acronyms (for example; GB, PRC, ROK), the CONVERT_TIMEZONE function treats them as time zone names, not time zone abbreviations.

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 adds one hour in the summer.

Using a time zone abbreviation

To view a current and complete list of time zone abbreviations, run the following command.

select pg_timezone_abbrevs();

The results contain a comma-separated string with the time zone abbreviation, UTC offset, and indicator if the time zone observes daylight-savings (t or f). For example, the following snippet shows two resulting rows. The first row contains the abbreviation for Pacific Daylight Time PDT, with a -07:00:00 offset from UTC, and t to indicate it observes daylight-savings time. The second row contains the abbreviation for Pacific Standard Time PST, with a -08:00:00 offset from UTC, and f to indicate it doesn't observe daylight-savings time.

pg_timezone_abbrevs
--------------------
(PDT,-07:00:00,t)	
(PST,-08:00:00,f)	

Run the SQL statement to obtain the entire list and find an abbreviation based on its offset and daylight-savings indicator. Approximately 200 rows are returned.

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 doesn't adjust for any local seasonal protocol.

Using POSIX-style format

A POSIX-style time zone specification is in the form STDoffset or STDoffsetDST, where STD is a time zone abbreviation, offset is the numeric offset in hours west from UTC, and DST is an optional daylight-savings zone abbreviation. Daylight savings time is assumed to be one hour ahead of the given offset.

POSIX-style time zone formats use positive offsets west of Greenwich, in contrast to the ISO-8601 convention, which uses positive offsets east of Greenwich.

The following are examples of POSIX-style time zones:

set timezone to ‘xxx36’;

Examples

Many of the examples use the TICKIT sample data set. For more information, see Sample database.

The following example converts the timestamp value from the default UTC time zone to PST.

select convert_timezone('PST', '2008-08-21 07:23:54');
                     
 convert_timezone
------------------------
2008-08-20 23:23:54

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

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 timestamp is within the daylight time period, so the function returns the daylight time.

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 timestamp string from EST to PST:

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

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

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 timestamp to US Eastern Daylight Time because the target time zone uses a time zone name (America/New_York) and the timestamp is within the daylight time period.

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.

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)