Jooq LocalDateTime字段使用系统时区而不是会话时区 [英] Jooq LocalDateTime fields use system timezone instead of session timezone

问题描述

我正在使用jooq(v3.11.9)访问以UTC时间运行的MySQL数据库.我正在使用生成的实体，并且正在使用JSR-310时间类型.我在配置中使用的选项:

I'm using jooq (v3.11.9) to access a MySQL database that is running in UTC time. I've using generated entities and am using JSR-310 time types. The option I'm using in my config:

<javaTimeTypes>true</javaTimeTypes>

我的理解是MySQL datetime和timestamp类型都映射到LocalDateTime，这很有意义，因为MySQL不会随时间存储时区信息.但是，当我在不同时区(在我的情况下是EST)的计算机上运行查询时，日期全都在本地计算机时区中，即使会话时区为UTC .

My understanding is that the MySQLdatetime and timestamp types both map to LocalDateTime which makes sense as MySQL doesn't store timezone information with the times. However when I run queries on a machine in a different timezone (in my case EST) the dates are all in my local machine timezone even though the session timezone is UTC.

我已经确认会话时区为UTC

I've confirmed that the session timezone is UTC

dslContext.fetch("SELECT @@system_time_zone,  @@global.time_zone, @@session.time_zone;")

返回

|@@system_time_zone|@@global.time_zone|@@session.time_zone|
+------------------+------------------+-------------------+
|UTC               |SYSTEM            |SYSTEM             |
+------------------+------------------+-------------------+

时区转换示例:

dslContext.select(MY_TABLE.EPOCH_DT_TM, MY_TABLE.CREATION_TIMESTAMP).from(MY_TABLE).limit(1).fetch()

+-----------------------+-----------------------+
|epoch_dt_tm            |creation_timestamp     |
+-----------------------+-----------------------+
|2019-04-18T13:57:39.163|2019-09-24T16:06:47.754|
+-----------------------+-----------------------+
// CAST to STRING PROPERLY USES SESSION TIMEZONE 
dslContext.select(MY_TABLE.EPOCH_DT_TM.cast(org.jooq.impl.SQLDataType.VARCHAR(100)), MY_TABLE.CREATION_TIMESTAMP.cast(org.jooq.impl.SQLDataType.VARCHAR(100))).from(MY_TABLE).limit(1).fetch()
+--------------------------+--------------------------+
|cast                      |cast                      |
+--------------------------+--------------------------+
|2019-04-18 17:57:39.163000|2019-09-24 20:06:47.754000|
+--------------------------+--------------------------+

我生成的实体中的字段:

The fields in my generated entities:

    public final TableField<MyTableRecord, LocalDateTime> EPOCH_DT_TM = createField("epoch_dt_tm", org.jooq.impl.SQLDataType.LOCALDATETIME, this, "");
    public final TableField<MyTableRecord, LocalDateTime> CREATION_TIMESTAMP = createField("creation_timestamp", org.jooq.impl.SQLDataType.LOCALDATETIME.nullable(false).defaultValue(org.jooq.impl.DSL.field("CURRENT_TIMESTAMP(6)", org.jooq.impl.SQLDataType.LOCALDATETIME)), this, "");

所以我的问题是:

1. 这是预期的行为吗?不应在表中填充原始(无时区)日期的记录.由于某种原因，日期是否仍在后台转换为java.sql.Timestamp?

1. Is this expected behavior? Shouldn't the record get populated with the raw (non timezoned) date in the table. Are the dates still getting converted to java.sql.Timestamp under the hood for some reason?

如果这是预期的行为，是否有任何方法可以确保您获得会话时区中的日期，而与客户端计算机上的本地时区无关?很难在本地测试代码的行为是否取决于计算机时区.

If this is expected behavior is there any way to ensure that you get dates in the session timezone regardless of the local timezone on the client machine? It is very hard to test locally if the behavior of the code is dependent on the machine timezone.

预先感谢您的帮助.

推荐答案

我最近发现，根据所使用的数据库驱动程序，jOOQ在DateTime解析中可能会表现出一些奇怪的行为. jOOQ返回偏移日期时间不是Z的时间(UTC)

I recently found that depending on the database driver being used, jOOQ can exhibit some strange behavior in DateTime parsing. jOOQ returns offset date time as Z (UTC) even though it's not

具体来说，在我的情况下，使用不同的Postgres驱动程序会导致DefaultBinding.java接收具有时间戳的日历对象，但对其调用toString以便进行解析.事实证明，toString不打印时区，然后jOOQ推断出它是本地时间.

Specifically, in my case, using a different Postgres driver resulted in DefaultBinding.java receiving a calendar object that has a timestamp, but calling toString on it in order to parse. Turns out, toString does not print the timezone, then jOOQ inferred that it was in local time.

对我来说，DefaultBinding.java(我使用带时区的时间戳)中令人反感的行是:

For me, the offending lines in DefaultBinding.java (I was using a timestamp with timezone) were:

else if (type == OffsetDateTime.class) {
    result = (T) offsetDateTime(ctx.resultSet().getString(ctx.index()));
}

基于没有时区的情况，您可能在其他系列的另一条线上.

You may be on a different line in that series of else ifs based on not having a time zone.

在测试中，我还发现更改系统时间可以更改结果，但是更改会话时间却无济于事.

In my testing, I also found that changing the system time changed the result, but changing the session time did nothing.

对我来说幸运的是，切换到标准Postgres驱动程序解决了该问题.如果没有，我将研究OffsetDateTime的绑定重载，以修复toString的使用以及相关时区的剥离.不幸的是，除非您也正在使用SQL驱动程序而不是要升级或替换的SQL驱动程序，否则您可能需要走这条路.或者，您可以将其与时区一起存储，然后在从数据库中加载时转换为所需的时区.

Fortunately for me, a switch to the standard Postgres driver resolved the problem. If it didn't, I was going to look in to overloading the binding for OffsetDateTime to fix the use of toString and it's associated stripping of the relevant time zone. You may need to pursue that path, unfortunately, unless you too are using a SQL driver than could be upgraded or replaced. Or, you could store it with a timezone and then convert to the desired timezone when you load from the database.

这篇关于Jooq LocalDateTime字段使用系统时区而不是会话时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！