java - 在 PostgreSQL 中存储 OffsetDateTime 时如何禁用到 UTC 的转换

Question

鉴于此表:

CREATE TABLE a(
  t TIMESTAMP WITH TIME ZONE
);

这个简单的 JDBC 代码片段:

DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/dbname", "user", "password"
).use { connection ->
    val nowSomeTimeZone = OffsetDateTime.now(ZoneOffset.of("+4"))
    connection.prepareStatement("insert into a(t) values (?)").use { insertStmt ->
        insertStmt.setObject(1, nowSomeTimeZone)
        insertStmt.executeUpdate()
    }
    connection.createStatement().use { stmt ->
        stmt.executeQuery("select * from a").use { resultSet ->
            resultSet.next()
            val t = resultSet.getObject(1, OffsetDateTime::class.java)
            println("$nowSomeTimeZone -> $t")
        }
    }
}

在 JDBC 堆栈内部的某个地方，一定会发生从 +04:00 到 UTC 的自动转换，因为这是 println 输出:

2018-08-30T10:35:33.594+04:00 -> 2018-08-30T06:35:33.594Z

更奇怪的是，当我使用 psql 控制台客户端查看表时，它显示了另一个时区(这是我的本地时区)的时间戳:

$ psql -h localhost -U username
dbname=> select * from a;
             t              
----------------------------
 2018-08-30 08:35:33.594+02

为什么会发生这种转换？如何禁用它？

Answer 1

无法禁用转换，因为 PostgreSQL 服务器会删除时区信息并始终以 UTC 形式存储时间戳，即使您显式使用 TIMESTAMP WITH TIME ZONE 类型也是如此。

引用PostgreSQL documentation :

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

此外，文档指出:

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.

问题中描述的奇怪行为是因为

• JDBC 驱动程序始终返回 UTC 时间戳
• psql 控制台客户端在显示时间戳之前将其转换为用户的本地时区，在本例中为德国时间 (+02:00)

感谢@RobbyCornelissen 的见解。