Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Modify Timezone through Alibaba Cloud RDS for PostgreSQL

Modify Timezone through Alibaba Cloud RDS for PostgreSQL

This short article explains how to modify Timezone through Alibaba Cloud RDS for PostgreSQL.

By Digoal

This short article explains how to modify Timezone through Alibaba Cloud RDS for PostgreSQL.

Background

Timezone is a session-level timezone parameter set by the client. If no value is specified by the client, the timezone parameters set in the database or the database and user-level parameters will prevail.

PostgreSQL parameter priority

For example, run the following command:

postgres=> show timezone;  
   TimeZone      
---------------  
 Asia/Shanghai  
(1 row)  
  
postgres=> select now();  
              now                
-------------------------------  
 2020-01-31 12:09:25.660323+08  
(1 row)  
postgres=> set timezone='+9';  
SET  
postgres=> show timezone;  
 TimeZone   
----------  
 <+09>-09  
(1 row)  
  
postgres=> select now();  
             now                
------------------------------  
 2020-01-31 13:09:44.71925+09  
(1 row)

Alibaba Cloud RDS for PostgreSQL does not allow direct modification of the timezone parameters but allows modification on the default configuration through role or database-level parameters. For example:

postgres=> alter role all set timezone='Asia/Kolkata';  
ALTER ROLE  
postgres=> \q  
  
重新连接数据库  
psql  
psql (12.1, server 11.5)  
Type "help" for help.  
  
postgres=> select now();  
               now                  
----------------------------------  
 2020-01-31 09:16:27.699978+05:30  
(1 row)  
  
postgres=> show timezone;  
   TimeZone     
--------------  
 Asia/Kolkata  
(1 row)

Query the current database and role-level configuration:

postgres=> select * from pg_db_role_setting ;  
 setdatabase | setrole |        setconfig          
-------------+---------+-------------------------  
           0 |       0 | {TimeZone=Asia/Kolkata}  
(1 row)

Reset the timezone configuration at database and role-level:

postgres=> alter role all reset timezone;  
ALTER ROLE

Query Time Zones Supported by System

Use system tables to query the alias time zone configurations supported by the current database:

postgres=> select * from pg_timezone_names;  
                  name                  | abbrev | utc_offset | is_dst   
----------------------------------------+--------+------------+--------  
 Indian/Christmas                       | CXT    | 07:00:00   | f  
 Indian/Chagos                          | IOT    | 06:00:00   | f  
 Indian/Cocos                           | CCT    | 06:30:00   | f  
 Indian/Comoro                          | EAT    | 03:00:00   | f  
 Indian/Mayotte                         | EAT    | 03:00:00   | f  
...  
 GB                                     | GMT    | 00:00:00   | f  
 GMT0                                   | GMT    | 00:00:00   | f  
 MST                                    | MST    | -07:00:00  | f  
 Poland                                 | CET    | 01:00:00   | f  
 US/Mountain                            | MST    | -07:00:00  | f  
 US/Pacific                             | PST    | -08:00:00  | f  
 US/Arizona                             | MST    | -07:00:00  | f  
 US/Aleutian                            | HST    | -10:00:00  | f  
 US/Hawaii                              | HST    | -10:00:00  | f  
 US/Indiana-Starke                      | CST    | -06:00:00  | f  
 US/Alaska                              | AKST   | -09:00:00  | f  
 US/Central                             | CST    | -06:00:00  | f  
 US/Eastern                             | EST    | -05:00:00  | f  
 US/Samoa                               | SST    | -11:00:00  | f  
 US/East-Indiana                        | EST    | -05:00:00  | f  
 US/Michigan                            | EST    | -05:00:00  | f  
 US/Pacific-New                         | PST    | -08:00:00  | f  
 GMT-0                                  | GMT    | 00:00:00   | f  
 EST                                    | EST    | -05:00:00  | f  
 MET                                    | MET    | 01:00:00   | f  
 CST6CDT                                | CST    | -06:00:00  | f  
 Turkey                                 | EET    | 02:00:00   | f  
(1167 rows)

The time zone out of the supported range cannot be configured with the name parameters. Otherwise, an error will be reported:

postgres=> alter role all set timezone='abc';
ERROR:  22023: invalid value for parameter "TimeZone": "abc"
LOCATION:  call_string_check_hook, guc.c:10582

If the time zone is not in the alias, it can be replaced with a numeric value. For example:

postgres=> alter role all set timezone='+1:11';  
ALTER ROLE  
postgres=> \q  
  
 psql  
psql (12.1, server 11.5)  
Type "help" for help.  
  
postgres=> select now();  
               now                  
----------------------------------  
 2020-01-31 03:09:10.723871-01:11  
(1 row)

Time Zone Support File:

$PGHOME/share/timezonesets
postgres=> show timezone_abbreviations ;  
 timezone_abbreviations   
------------------------  
 Default  
(1 row)  
  
  
-> cd $PGHOME/share/timezonesets   
-> ll  
total 104K  
-rw-r--r-- 1 root root 6.9K Nov 30 21:31 Africa.txt  
-rw-r--r-- 1 root root  11K Nov 30 21:31 America.txt  
-rw-r--r-- 1 root root 1.2K Nov 30 21:31 Antarctica.txt  
-rw-r--r-- 1 root root 8.2K Nov 30 21:31 Asia.txt  
-rw-r--r-- 1 root root 3.5K Nov 30 21:31 Atlantic.txt  
-rw-r--r-- 1 root root 1.2K Nov 30 21:31 Australia  
-rw-r--r-- 1 root root 3.3K Nov 30 21:31 Australia.txt  
-rw-r--r-- 1 root root  27K Nov 30 21:31 Default  
-rw-r--r-- 1 root root 1.3K Nov 30 21:31 Etc.txt  
-rw-r--r-- 1 root root 8.6K Nov 30 21:31 Europe.txt  
-rw-r--r-- 1 root root  593 Nov 30 21:31 India  
-rw-r--r-- 1 root root 1.3K Nov 30 21:31 Indian.txt  
-rw-r--r-- 1 root root 3.7K Nov 30 21:31 Pacific.txt

Summary

Use alter role all set timezone='' to set the default time zone for the client of Alibaba Cloud RDS for PostgreSQL.

References

PostgreSQL Database ApsaraDB RDS for PostgreSQL Timezone Client Parameter Alter Role All
0 0 0
Share on

Read previous post:

Parameter Priority in PostgreSQL

Read next post:

A Method to Make MySQL Bit(n) Compatible with PostgreSQL

digoal

282 posts | 24 followers

You may also like

Comments

digoal

282 posts | 24 followers

Related Products

More Posts by digoal

See All