postgresql 客户端连接方式差异造成的时间差异

一、现象

在连接postgresql数据库时，会使用JDBC和ODBC的两类程序连接，下面会分别列出不同客户端连接后的表现。

在写本篇内容时，是北京时间21点44分。

A.使用JDBC的程序（这里使用DBeaver做演示），通过select now() 可以查看到正确的时间。

B.使用ODBC的程序（这里使用Navicat做演示），通过selec now() 看到的时间确实8小时之前。

C.使用客户端的psql程序，连接至服务端，时间错误。

D.在服务端本地登录，时间错误。

上述过程中，只有使用了JDBC的DBeaver查看到的时间是正确的。其他使用了ODBC连接的客户端，表现的时间都是错误的。

因此可以有两个推测与疑点

1.JDBC与ODBC的连接机制不同。

2.Postgres本身的时区是否异常。

二、Postgresql设置 1.查看设置

为了避免客户端连接的差异，在服务端通过本地psql连接，进行状态查看。

查看时区

postgres=# show timezone;
 TimeZone 
----------
 Etc/UTC
(1 row)

查看配置文件时区，配置文件postgresql.conf 。

timezone = 'Etc/UTC'

可支持的时区

可通过视图 pt_timezone_names查看pg所支持的时区。

2.修改Postgresql时区         会话级               

postgres=# set time zone ‘PRC’;
set

        用户级

alter role rolname set timezone=‘UTC’;
或者
alter role all set timezone=‘UTC’;

        数据库级

alter database dbname set timezone=‘UTC’;

        全局配置

修改配置文件postgresql.conf

timezone = 'PRC'

PRC是People's Republic of China的缩写。

这里说明了如何从postgres这一层确认与修改时区，如此修改之后，ODBC获取时间正常了，但疑惑并未完全解答，需要进一步分析。

三、PG的JDBC 1.PG的时区

        在postgres官网，针对客户端连接可以看到这样一段描述。

PostgreSQL: Documentation: 9.1: Client Connection Defaults

timezone (string)

Sets the time zone for displaying and interpreting time stamps. If not explicitly set, the server initializes this variable to the time zone specified by its system environment. See Section 8.5.3 for more information.

基本大意是 如果没有显式设置timezone，则服务器将该变量初始化为其系统环境指定的时区。

针对服务端的描述

PostgreSQL: Documentation: 9.1: Date/Time Types

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

所有时区的日期和时间都存储在UTC内部。在显示给客户端之前，需要先转换为timezone配置参数指定的时区内的本地时间。

2.JAVA与时区

综合前文得出，DBeaver在通过JDBC连接时，一定是使用了某个参数，改变了会话级别的timezone，使得结果与ODBC有所差别。因此，我们强行给jdbc连接增加时区参数。

JAVA代码

package HelloPostgres.HelloPostgres;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
	public static void main(String args[]) {
		Connection c = null;
		Statement stmt = null;
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager.getConnection("jdbc:postgresql://192.168.130.219:55433/gdhsabisadm", "username",
					"password");
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("Opened database successfully");

		try {
			stmt = c.createStatement();
			ResultSet rs = stmt.executeQuery("select now()");
			while (rs.next()) {
				String now = rs.getString("now");

				System.out.println(now);
			}

			rs.close();
			stmt.close();

			c.close();

		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("查询数据成功！");

	}
}

执行结果

Opened database successfully
2022-04-26 23:42:15.301302+08
查询数据成功！

在jdbc连接串增加   "?TimeZone=Etc/UTC"，执行结果

Opened database successfully
2022-04-26 23:43:08.889694+08
查询数据成功！

貌似没有发生变化。

我们再从更底层的JVM尝试一下，在启动参数中增加  "-Duser.timezone=Etc/UTC"

Opened database successfully
2022-04-26 15:45:27.217254+00
查询数据成功！

时间已经是8小时之前了，实验成功。当前只是确认了jvm的时区参数生效，那么要继续分析一下，java程序连接pg时，使用了什么样的timezone参数。

        通过jvisualvm工具，查看进程24300（DBeaver进程），可以看到系统级的用户属性 user.timezone=Asia/Shanghai 。

四、总结

明确了JDBC连接pg库ODBC查看时间不同的原因。

对时区与PG、时区与JDBC和jvm有了进一步了解。

五、参考资料

World Clock — current time around the world

World Clock - Local Time, World Time, Time Zone & Weather.

转:时区缩写 - Tracy. - 博客园