PostgreSQL 裡面的時區設定使用筆記

PostgreSQL 的時區，內建預設是 GMT。不過時區會在 initdb 時，根據作業系統設定值決定，然後設定在 $PGDATA/postgesql.conf 裡面的 GUC 參數 timezone 上。而可以設置的時區資訊則紀錄在 pg_timezone_names 這個系統表裡面。

下面在 CentOS 底下，在 OS 和 DB 中，列舉這些資訊出來作比較

bash-$ date +%Z
UTC
bash-$ cat $PGDATA/postgresql.conf |grep timezone
log_timezone = 'UTC'
timezone = 'UTC'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # share/timezonesets/.
bash-$ psql -h localhost -p 5444 -U enterprisedb -d edb
Password: 
psql.bin (9.5.5.10)
Type "help" for help.


edb=# SHOW timezone;
 TimeZone 
----------
 UTC
(1 row)
 
edb=# \d pg_timezone_names
View "pg_catalog.pg_timezone_names"
   Column   |   Type   | Modifiers 
------------+----------+-----------
 name       | text     | 
 abbrev     | text     | 
 utc_offset | interval | 
 is_dst     | boolean  | 


edb=# SELECT * FROM pg_timezone_names
  WHERE utc_offset = '+08:00:00'::INTERVAL;  
        name        | abbrev | utc_offset | is_dst 
--------------------+--------+------------+--------
 Etc/GMT-8          | +08    | 08:00:00   | f
 Australia/Perth    | AWST   | 08:00:00   | f
 Australia/West     | AWST   | 08:00:00   | f
 Asia/Harbin        | CST    | 08:00:00   | f
 Asia/Kuala_Lumpur  | MYT    | 08:00:00   | f
 Asia/Ulan_Bator    | ULAT   | 08:00:00   | f
 Asia/Chongqing     | CST    | 08:00:00   | f
 Asia/Makassar      | WITA   | 08:00:00   | f
 Asia/Chungking     | CST    | 08:00:00   | f
 Asia/Manila        | PHT    | 08:00:00   | f
 Asia/Irkutsk       | +08    | 08:00:00   | f
 Asia/Ulaanbaatar   | ULAT   | 08:00:00   | f
 Asia/Choibalsan    | CHOT   | 08:00:00   | f
 Asia/Brunei        | BNT    | 08:00:00   | f
 Asia/Shanghai      | CST    | 08:00:00   | f
 Asia/Ujung_Pandang | WITA   | 08:00:00   | f
 Asia/Taipei        | CST    | 08:00:00   | f
 Asia/Singapore     | SGT    | 08:00:00   | f
 Asia/Hong_Kong     | HKT    | 08:00:00   | f
 Asia/Kuching       | MYT    | 08:00:00   | f
 Asia/Macau         | CST    | 08:00:00   | f
 Asia/Macao         | CST    | 08:00:00   | f
 Antarctica/Casey   | +08    | 08:00:00   | f
 Hongkong           | HKT    | 08:00:00   | f
 PRC                | CST    | 08:00:00   | f
 ROC                | CST    | 08:00:00   | f
 Singapore          | SGT    | 08:00:00   | f
(27 rows)

當登入資料庫的客戶端的時區和主機設置時區不一樣會怎麼樣？
其實還是根據資料庫的 timezone 設置時區來回傳時間，因此只要與資料庫存取時戳時，固定使用資料庫裡面的時間函數，就能保持資料庫內部時間的一致。以下從其他電腦登入資料庫查看：

cchsu@cchsu-PC ~
$ psql -h 130.xxx.2xx.xxx -p 5444 -U enterprisedb -d edb
用戶 enterprisedb 的密碼:
psql (9.6.1，伺服器 9.5.5.10)
輸入 "help" 顯示說明。
 
edb=# SELECT now();
               now
----------------------------------
 19-NOV-16 13:44:20.056124 +00:00
(1 筆資料列)
 
edb=#

如果要讓遠端登入後，能夠依照客戶端時區與資料庫互動要怎麼做？timezone 這個 GUC 更動後，只要重新載入，無須重新啟動資料庫，就能生效。因此它基本上可以在一個 Session，或對個別帳號/個別 Database 進行設定。

edb=# -- 依照 Session 設定
edb=# set timezone to 'ROC';           
SET
edb=# -- 設定某帳號預設時區
edb=# ALTER ROLE enterprisedb SET timezone TO 'ROC';
ALTER ROLE
edb=# -- 設定某 Database 的預設時區
edb=# ALTER DATABASE edb SET timezone TO 'ROC';
ALTER DATABASE

以下試驗不同時區設定的 Session，在資料讀寫上的行為。
這一段的效果是，在 Session 內設置 GUC 可以讓取得的時間作轉換


接著用時區簡稱來設置：時區簡稱可以從系統表 pg_timezone_names 得知


如果是使用各程式語言的 Library 提供的 API 進行開發（例如 JDBC 或 ADO.Net 等），也都能夠即時設定 timezone 參數的。詳請見各相關手冊。

結論： PostgreSQL 內存儲的帶時區的時間固定下來，要對資料切換不同時區的方式，就是在一個連線階段（Session）裡面設置 GUC 參數；或是，把某帳戶的時區設定為某某時區，這樣該帳號就自動以某時區的方式活動了


參考：

ubuntu 10.04 - How to check which timezone in Linux? - Super User

PostgreSQL: Documentation: 9.5: Client Connection Defaults

PostgreSQL中文社区: 世界上功能最强大的开源数据库...PostgreSQL Oracle兼容性之 - NEW_TIME , SYS_EXTRACT_UTC
在 PostgreSQL 時區轉換及計算時間 | 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY
Postgresql Interval, Date, Timestamp and Time Data Types - 2ndQuadrant | PostgreSQL

Timestamps and Time Zones in PostgreSQL · Philipe Fatio