8 时间转指定时区的时间_PostgreSQL时区设置引起的时间错误

最近遇到一个时间处理的问题，格式化后的时间戳字符串与最后作为输入得到的时间戳不一致，本文尝试解释其原因和解决办法。

1、错误现象

当前环境 set timezone = prc; 或者 set timezone = "Asia/Shanghai";

postgres=# select current_timestamp,                                                                                           to_char(current_timestamp, 'YYYY/MM/DD HH24:MI:SS.US TZ'),                                                                     to_char(current_timestamp, 'YYYY/MM/DD HH24:MI:SS.US TZ')::timestamp with time zone;-[ RECORD 1 ]-----+-------------------------------current_timestamp | 2020-05-08 21:12:44.808308+08to_char           | 2020/05/08 21:12:44.808308 CSTto_char           | 2020-05-09 11:12:44.808308+08

可以看到，第二次作为timestamp with time zone的输入之后，时间发生了改变。

2、时区的输出

上边的例子中，中间转换函数to_char得到的时区名为CST，这并不是错误。因为时区简写不是国际标准，所以允许重复，就像CST，它不止表示中国标准时间(China Standard Time)，还可以是美国中部标准时间(Central Standard Time)、澳大利亚中部标准时间(Central Standard Time)、以及古巴中部标准时间(Cuba Central Standard Time)。

上述例子中，当做类型转换时，很明显是把CST当作了美国中部时间(西六区)，所以最后结果是中国标准时间东八区的凌晨5:52。基本可以认定，这里的问题是由于输出和输入对时区简称解释不一致引起的。

3、输出

时区文件有自己的特定格式，处理代码位于 src/timezone/pgtz.c，在我们刚才的时区设置中，确实是最终得到名为CST的时区。没有花太多时间去阅读时区文件定义的文档，所以目前只是确定了结果，当我们设置中国东八区时，将简称CST作为输出结果，从定义来说这不是错误。更多资料可以阅读 man 3 tzfile。

4、解决办法一

将时区输出格式TZ改为TZH，输出时区偏移量而不是名字，上例的结果将是“2020/05/08 15:52:57.526665 +08”，可以保证最终结果正确。

postgres=# select current_timestamp,                                                                                           to_char(current_timestamp, 'YYYY/MM/DD HH24:MI:SS.US TZH'),                                                                    to_char(current_timestamp, 'YYYY/MM/DD HH24:MI:SS.US TZH')::timestamp with time zone;-[ RECORD 1 ]-----+-------------------------------current_timestamp | 2020-05-08 21:15:21.784459+08to_char           | 2020/05/08 21:15:21.784459 +08to_char           | 2020-05-08 21:15:21.784459+08

5、解决办法二

有一个参数timezone_abbreviations，在PG v12文档“B.4. Date/Time Configuration Files”有详细解释，有兴趣自行阅读。直接给出办法，找到Default文件的CST那一行，将“-21600”(它就是错误的成因)改为“28800”，已经加载过时区的会话可能需要重开生效。

6、解决办法三

将时区Asia.txt拷贝为Asia，文档有解释，因为带句点名字不合法。这时无法set timezone = "Asia"直接加载，因为文件中有设置冲突，比如有两个IST，一个是伊朗时间一个是印度时间，需要手动注释掉一个，其他还有几个重名简称，根据错误提示挨个解决就好。

后两种办法，CST全部解释成中国标准时间，可能导致其它三个时区使用CST时的输入错误，因此最好的办法还是按照 TZH格式表示，才不会产生歧义的结果。

但为君故，沉吟至今。