当前位置：首页 > news >正文

HGDB数据库时区修改

news 2026/3/26 22:15:10

文章目录

环境
文档用途
详细信息

环境

系统平台：N/A
版本：4.1.1,4.3.2

文档用途

用于指导HGDB修改时区的操作

详细信息

1、查看系统时间与时区

–linux系统

[root@localhost ~]# date -R

Fri, 27 Jul 2018 14:37:48 +0800

–Windows系统

控制面板–>日期和时间–>时区，可以看到当前时区

2、查看HGDB数据库的时区与时间

highgo=# select now();now-------------------------------2018-07-2714:48:35.576102+08(1行记录)highgo=# show time zone;TimeZone----------------Asia/Hong_Kong(1行记录)

3、查看HGDB数据库所有可供选择的时区

highgo=# select * from pg_timezone_names;name|abbrev|utc_offset|is_dst----------------------------------+--------+------------+--------Africa/Abidjan|GMT|00:00:00|f Africa/Accra|GMT|00:00:00|f Africa/Addis_Ababa|EAT|03:00:00|f Africa/Algiers|CET|01:00:00|f Africa/Asmara|EAT|03:00:00|f Africa/Asmera|EAT|03:00:00|f Africa/Bamako|GMT|00:00:00|f Africa/Bangui|WAT|01:00:00|f Africa/Banjul|GMT|00:00:00|f Africa/Bissau|GMT|00:00:00|f Africa/Blantyre|CAT|02:00:00|f Africa/Brazzaville|WAT|01:00:00|f Africa/Bujumbura|CAT|02:00:00|f Africa/Cairo|EET|02:00:00|f Africa/Casablanca|WEST|01:00:00|t Africa/Ceuta|CEST|02:00:00|t Africa/Conakry|GMT|00:00:00|f......UCT|UCT|00:00:00|f Universal|UTC|00:00:00|f US/Alaska|AKDT|-08:00:00|t US/Aleutian|HDT|-09:00:00|t US/Arizona|MST|-07:00:00|f US/Central|CDT|-05:00:00|t US/East-Indiana|EDT|-04:00:00|t US/Eastern|EDT|-04:00:00|t US/Hawaii|HST|-10:00:00|f US/Indiana-Starke|CDT|-05:00:00|t US/Michigan|EDT|-04:00:00|t US/Mountain|MDT|-06:00:00|t US/Pacific|PDT|-07:00:00|t US/Pacific-New|PDT|-07:00:00|t US/Samoa|SST|-11:00:00|f UTC|UTC|00:00:00|f W-SU|MSK|03:00:00|f WET|WEST|01:00:00|t Zulu|UTC|00:00:00|f(593行记录)

4、数据库全局时区的修改

确定当前要修改为哪个时区，在上面结果中找到对应的时区名称，修改配置文件$PG_DATA/postgresql.conf中的参数timezone和log_timezone的值，然后重新加载配置文件即可。

5、修改数据库和用户级别的时区

--查看当前数据库全局时区highgo=# show time zone;TimeZone-----------US/Alaska(1行记录)--修改当前名为highgo的数据库时区highgo=# alter database highgo set timezone='Asia/Hong_Kong';ALTERDATABASE--查询修改结果highgo=# select * from pg_db_role_setting;setdatabase|setrole|setconfig-------------+---------+---------------------------12428|0|{TimeZone=Asia/Hong_Kong}(1行记录)--修改角色highgo的时区highgo=# alter role highgo set timezone='US/Aleutian';ALTERROLE--查看修改后的效果highgo=# select * from pg_db_role_setting;setdatabase|setrole|setconfig-------------+---------+---------------------------12428|0|{TimeZone=Asia/Hong_Kong}0|10|{TimeZone=US/Aleutian}(2行记录)--查看全局时区highgo=# show time zone;TimeZone-----------US/Alaska(1行记录)

HGDB可以设置数据库、角色、会话级别的时区，会话级别的仅对当前会话起作用，同理角色及数据库级别的仅对设置的角色及数据库起作用，但不会影响设置时连接的会话，需要重新连接角色或数据库后才会生效。

6、时区修改后，表中已有的数据，只有TIMESTAMP WITH time zone类型会随时区的变化而改变，如果是time WITH time zone类型，可以在查询时直接转换为当前使用的时区，例如：

highgo=# SELECT '2018-08-01 14:00:00+08' at time zone 'US/Alaska';timezone---------------------2018-07-3122:00:00(1行记录)

如需将其他的时间类型修正到修改后的时区，可参考如下步骤：

（1）计算两个时区间的时间差

两时区间的时间差=(当前时区-目标时区)

目标时区的时间=当前时区的时间-(当前时区-目标时区)，(注:东时区为正，西时区为负)

例：当前时区为Asia/Hong_Kong，即东8区（UTC/GMT+08:00），目标时区为US/Alaska，即西8区（UTC/GMT-08:00）

两时区间的时间差=(+8)-(-8)=16

已知东八区当前时间为2018-08-01 12:00，计算西八区的时间，

西八区时间=2018-08-01 12:00-((+8)-(-8))=2018-07-31 20:00:00-08

（2）根据上面的公式计算出时间差，编写update语句，批量更新数据即可。

7、修改时区对数据的影响

修改时区后，数据类型“TIMESTAMP WITH time zone”会随时区发生变化，其余时间类型不会跟随时区改变。

以下实验说明了这种影响

--查看当前时区（东八区）highgo=# show time zone;TimeZone----------------Asia/Hong_Kong(1行记录)--创建测试表highgo=# CREATE TABLE test_zone ( id NUMERIC, tpz TIMESTAMP WITH time zone, tpoutz TIMESTAMP, twz time WITH time zone, twoz time without time zone );CREATETABLE--插入测试数据highgo=# INSERT into test_zone VALUES(1,'2018-08-1 12:00:00','2018-08-1 12:00:00','2018-08-1 12:00:00','2018-08-1 12:00:00');INSERT01highgo=# INSERT into test_zone VALUES(2,'2018-08-1 13:00:00','2018-08-1 13:00:00','2018-08-1 13:00:00','2018-08-1 13:00:00');INSERT01highgo=# INSERT into test_zone VALUES(3,'2018-08-1 14:00:00','2018-08-1 14:00:00','2018-08-1 14:00:00','2018-08-1 14:00:00');INSERT01highgo=#highgo=# SELECT * from test_zone;id|tpz|tpoutz|twz|twoz----+------------------------+---------------------+-------------+----------1|2018-08-0112:00:00+08|2018-08-0112:00:00|12:00:00+08|12:00:002|2018-08-0113:00:00+08|2018-08-0113:00:00|13:00:00+08|13:00:003|2018-08-0114:00:00+08|2018-08-0114:00:00|14:00:00+08|14:00:00(3行记录)--修改当前时区（改为西八区）log_timezone='US/Alaska'timezone='US/Alaska'pg_ctl reload-D D:\highgo\database\4.3.2\data--再次查询结果highgo=# show time zone;TimeZone-----------US/Alaska(1行记录)highgo=# SELECT * from test_zone;id|tpz|tpoutz|twz|twoz----+------------------------+---------------------+-------------+----------1|2018-07-3120:00:00-08|2018-08-0112:00:00|12:00:00+08|12:00:002|2018-07-3121:00:00-08|2018-08-0113:00:00|13:00:00+08|13:00:003|2018-07-3122:00:00-08|2018-08-0114:00:00|14:00:00+08|14:00:00(3行记录)--由以上结果可知，仅有tpz列（数据类型为TIMESTAMP WITH time zone）跟随时区发生了改变，其余列（数据类型分别为：TIMESTAMP、time WITH time zone、time without time zone）未根据时区发生改变。--根据当前的时区修正数据，两个时区间相差16小时，需要更新的字段为tpoutz、twz、twoz，语句如下UPDATEtest_zonesettpoutz=tpoutz-INTERVAL'16 hours',twz=twz attimezone'US/Alaska',twoz=twoz-INTERVAL'16 hours'--数据更新之后，查询结果如下highgo=# SELECT * from test_zone;id|tpz|tpoutz|twz|twoz----+------------------------+---------------------+-------------+----------1|2018-07-3120:00:00-08|2018-07-3120:00:00|20:00:00-08|20:00:002|2018-07-3121:00:00-08|2018-07-3121:00:00|21:00:00-08|21:00:003|2018-07-3122:00:00-08|2018-07-3122:00:00|22:00:00-08|22:00:00(3行记录)