jOOQ时间戳与本地时区偏移量一起存储 [英] jOOQ Timestamp being stored with local Timezone offset

问题描述

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

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

我有一个表，该表的列类型为timezone without time zone，这将生成具有适用的java.util.Timestamp属性的Object.

I have a table with a column of type timezone without time zone, this generates my Object with the applicable java.util.Timestamp property.

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

What I'm seeing, during insert, is jOOQs binding process converting a java.util.Timestamp into a date with local timezone offset.

例如，对于unix时间戳记1421109419(格林尼治标准时间2015年1月13日)，该属性设置为new Timestamp(1421109419 * 1000).

eg for a unix timestamp 1421109419 (13 Jan 2015 00:36:59 GMT) the property is set with new Timestamp(1421109419 * 1000).

从jOOQ记录器中我看到:

from the jOOQ logger I see:

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".

and sure enough in the record is the value "2015-01-13 13:36:59".

该软件正在NZDT的计算机上运行，​​解释了+13偏移量.

The software is running on a machine in NZDT which explains the +13 offset.

鉴于时间是在TimeZone不可知容器(Timestamp)中提供的，我希望在创建insert语句时能够兑现这一点.

Given the time is being supplied in a TimeZone agnostic container (Timestamp) I would have expected that to be honoured when creating the insert statement.

我如何让jOOQ创建不是本地时间的时间戳?

How can I have jOOQ create timestamps NOT in local time?

推荐答案

不幸的是，您遇到了一些不利于您的事情:

Unfortunately you have a few things working against you:

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

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

Because of these two issues, if you have two different JVMs -- one using Los Angeles time and the other using New York time -- whenever you write a TIMESTAMP with one JVM it will be a different "UTC time" in the other JVM. TIMESTAMP takes the adjusted value and just uses it as given. If you change your TIMESTAMP columns to be TIMESTAMPTZ then the same time in both JVMs will always be the same UTC time.

如果查看Postgres JDBC驱动程序的ConnectionFactoryImpl#openConnectionImp，您会看到它在哪里将本地JVM的时区设置为数据库服务器会话区的时区.

If you look at the Postgres JDBC Driver's ConnectionFactoryImpl#openConnectionImp you can see where it sets your local JVM's time zone as the time zone for the database server's session zone.

因此，解决此问题的唯一明智的方法是仅使用TIMESTAMPTZ而不是TIMESTAMP.这是有关此的更多信息:

So the only sane way to deal with this is to only ever use TIMESTAMPTZ instead of TIMESTAMP. Here's some more information on this:

PostgreSQL/JDBC和TIMESTAMP与TIMESTAMPTZ的对比

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

这篇关于jOOQ时间戳与本地时区偏移量一起存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！