修改 PostgreSQL 数据库时区

总结概览

方法	修改对象	生效范围	持久性	备注
1. 修改 `postgresql.conf`	数据库服务器	所有数据库	永久	需要重启数据库服务，是最根本的解决方案。
2. 使用 `ALTER DATABASE`	单个数据库	指定数据库的所有连接	永久	只影响特定数据库，无需重启服务。
3. 使用 `ALTER USER`	单个用户	指定用户的所有连接	永久	只影响特定用户，无需重启服务。
4. 在会话中设置 `TimeZone`	当前连接	当前会话（连接）	临时	仅在本次连接中有效，断开后失效。

方法一：修改配置文件（永久生效，影响整个服务器）

这是最彻底的方法，会改变整个 PostgreSQL 实例所有数据库的默认时区。

找到配置文件 postgresql.conf
- 它的位置因安装方式而异。常见位置包括：
  - Linux: /etc/postgresql/<version>/main/postgresql.conf
  - Windows: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf
- 你也可以在 PostgreSQL 中运行以下命令来查找：
```
SHOW config_file;
```
编辑配置文件
- 找到 timezone 参数。默认它可能被注释掉（行首有 #）。
- 修改或添加该行，将其设置为所需的时区。时区名称列表可以查看 pg_timezone_names 系统视图。
- 例如，设置为北京时间（东八区）：
```
timezone = 'Asia/Shanghai'
```
  也可以使用 UTC 偏移量，但推荐使用具名时区（如 Asia/Shanghai），因为它能自动处理夏令时（DST）变化。
```
timezone = 'PRC' -- 'PRC' 也是中国时区的别名
```
重启 PostgreSQL 服务
- 修改配置文件后，必须重启服务才能使更改生效。
- Linux (Systemd):
```
sudo systemctl restart postgresql
# 或者指定版本
sudo systemctl restart postgresql@14-main
```
- Windows (服务管理器):
  打开“服务”，找到 “PostgreSQL” 服务，右键选择“重新启动”。

验证：
重启后，重新连接数据库，执行：

SHOW timezone;

结果应该显示 Asia/Shanghai。

方法二：修改数据库的默认时区

如果你只想更改某个特定数据库的默认时区，可以使用 SQL 命令。

-- 连接到你的目标数据库（例如 mydb）
\c mydb

-- 修改该数据库的默认时区配置
ALTER DATABASE mydb SET timezone TO 'Asia/Shanghai';

注意：

此设置是永久的，会被写入系统目录。
它只对此后新建立的连接到该数据库的会话有效。现有连接不会受影响，需要重连后才能生效。
无需重启数据库服务。

方法三：修改用户的默认时区

如果你只想为某个特定用户设置时区，可以使用以下命令。

ALTER USER your_username SET timezone TO 'Asia/Shanghai';

注意：

此设置是永久的。
它只对此后该用户新建立的会话有效。
用户级别的设置会覆盖数据库级别和服务器级别的设置。

方法四：在当前会话中临时修改

这只在你当前的数据库连接中有效，一旦断开连接，设置就会失效。非常适合临时查询或调试。

-- 方法 1：使用 SET 命令
SET timezone TO 'Asia/Shanghai';

-- 方法 2：设置 TIMEZONE 变量
SET TIMEZONE = 'Asia/Shanghai';

-- 方法 3：设置 session 级别的参数
SET SESSION timezone = 'Asia/Shanghai';

验证：
设置后，你可以立即查看当前时间来确认：

SELECT NOW();

或者再次查看时区设置：

SHOW timezone;

重要提示和常见问题

时区名称：务必使用正确的时区名称。你可以查询系统视图来获取所有可用的时区：
```
SELECT * FROM pg_timezone_names;
```
对于中国时间，常用的是 Asia/Shanghai 或 PRC。
优先级：会话级设置 > 用户级设置 > 数据库级设置 > 服务器级设置。如果一个用户在连接时指定了时区，它会覆盖所有其他默认设置。
timestamp with time zone vs timestamp without time zone：
- 时区设置主要影响 timestamp with time zone (timestamptz) 类型的显示和输入。数据库内部存储的是 UTC 时间，会根据 timezone 设置转换成相应的时间显示给你。
- 对于 timestamp without time zone (timestamp) 类型，时区设置完全不影响它，因为它根本不包含时区信息。
应用连接：大多数编程语言或 ORM 框架（如 Python/Psycopg2, Java/JDBC）在建立连接时，也会设置自己的时区。有时应用层的时区设置会覆盖数据库的会话设置，如果遇到问题，也需要检查应用端的配置。