postgresql - jOOQ 时间戳与本地时区偏移一起存储

Question

PostgreSQL 9.3/postgresql-9.3-1100-jdbc41.jar

我有一个表，其中有一列类型为 timestamp without time zone，这会生成具有适用的 java.util.Timestamp 属性的对象。

在插入期间，我看到的是 jOOQ 的绑定(bind)过程将 java.util.Timestamp 转换为具有本地时区偏移的日期。

例如，对于 unix 时间戳 1421109419(2015 年 1 月 13 日 00:36:59 GMT)，属性设置为 new Timestamp(1421109419 * 1000)。

从我看到的 jOOQ 记录器中:

2015-01-13 14:14:31,482 DEBUG [http-bio-8002-exec-4] org.jooq.tools.LoggerListener#debug:255 - -> with bind values      : insert into "foo"."bar" ("start_date") values (timestamp '2015-01-13 13:36:59.0') returning "foo"."bar"."id"

2015-01-13 14:14:31,483 TRACE [http-bio-8002-exec-4] org.jooq.impl.DefaultBinding#trace:179 - Binding variable 3       : 2015-01-13 13:36:59.0 (class java.sql.Timestamp)

果然记录中的值是“2015-01-13 13:36:59”。

该软件在 NZDT 的机器上运行，它解释了 +13 偏移量。

鉴于时间是在与 TimeZone 无关的容器(时间戳)中提供的，我希望在创建插入语句时能够兑现这一点。

我怎样才能让 jOOQ 创建时间戳而不是本地时间？

Answer 1

不幸的是，有些事情对您不利:

1. PostgreSQL JDBC 驱动程序将时区设置为您在 Postgres session 中的 JVM 时区。因此，即使您的数据库服务器以 UTC 运行，也会使用 JVM 的时区插入一个 TIMESTAMP 字段。当您插入或查询数据时，数据库服务器将始终使用 JVM 时区。
2. 您正在使用 TIMESTAMP 而不是 TIMESTAMPTZ。这些类型的描述并不反射(reflect)它们的实际用途。 TIMESTAMPTZ 实际上意味着时区不可知论。无论您插入什么值，它都将使用 session 时区调整为 UTC。

由于这两个问题，如果您有两个不同的 JVM——一个使用洛杉矶时间，另一个使用纽约时间——每当您用一个 JVM 编写 TIMESTAMP 时，它就会是不同的“UTC 时间”其他 JVM。 TIMESTAMP 采用调整后的值并按给定的方式使用它。如果您将 TIMESTAMP 列更改为 TIMESTAMPTZ，那么两个 JVM 中的同一时间将始终是相同的 UTC 时间。

如果您查看 Postgres JDBC 驱动程序的 ConnectionFactoryImpl#openConnectionImp，您可以看到它将本地 JVM 的时区设置为数据库服务器 session 区的时区。

所以处理这个问题的唯一明智的方法是只使用 TIMESTAMPTZ 而不是 TIMESTAMP。这里有一些关于此的更多信息:

PostgreSQL/JDBC and TIMESTAMP vs. TIMESTAMPTZ

http://justatheory.com/computers/databases/postgresql/use-timestamptz.html