作者
digoal
日期
2020-01-31
标签
PostgreSQL , timezone , client parameter , alter role all
背景
timezone 是客户端设定的会话级别的时区参数。如果客户端没有指定，则以数据库设置的timezone参数或db，user级别参数为准。
例如
```
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)
```
阿里云rds pg不允许直接修改timezone参数，但是可以通过role或者db级别进行默认配置修改。例如：
```
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)
```
查询当前db,role级别配置：
postgres=> select * from pg_db_role_setting ;
setdatabase | setrole | setconfig
-------------+---------+-------------------------
0 | 0 | {TimeZone=Asia/Kolkata}
(1 row)
重置timezone db,role级别设置：
postgres=> alter role all reset timezone;
ALTER ROLE
查询系统支持哪些时区
通过系统表查询当前数据库支持哪些别名时区设置，
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)
不在支持范围的时区无法使用name设置，否则会报错:
postgres=> alter role all set timezone='abc';
ERROR: 22023: invalid value for parameter "TimeZone": "abc"
LOCATION: call_string_check_hook, guc.c:10582
如果你的时区不在别名这里，可以直接用数值代替，例如：
```
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)
```
时区支持文件$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
```
小结
使用alter role all set timezone=''设置阿里云rds pg的客户端默认时区。
参考
https://www.postgresql.org/docs/12/datetime-config-files.html
https://www.postgresql.org/docs/12/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
