By Digoal
This short article explains how to 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.
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
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
Use alter role all set timezone=''
to set the default time zone for the client of Alibaba Cloud RDS for PostgreSQL.
ApsaraDB - September 27, 2021
digoal - August 3, 2021
Alibaba Clouder - May 6, 2019
digoal - May 2, 2021
digoal - May 19, 2021
Alibaba Clouder - July 12, 2018
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreFully managed and less trouble database services
Learn MoreAn on-demand database hosting service for MySQL, SQL Server and PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by digoal