Modify Timezone through Alibaba Cloud RDS for PostgreSQL

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

• https://www.postgresql.org/docs/12/datetime-config-files.html
• https://www.postgresql.org/docs/12/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

Hi there, I really like it. Thanks for sharing those images with us. You made my day. I was actually searching for this https://takeitpersonelly.com/2021/10/18/how-a-student-entrepreneur-can-get-more-motivated/ website online because I want to know how a student entrepreneur can get more motivated. It is regarding my college project. When I was looking for it online on google search, I found your post link as well and can't stop myself by praising you for sharing those images with you.