jOOQ返回偏移日期时间为Z(UTC)，即使不是 [英] jOOQ returns offset date time as Z (UTC) even though it's not

问题描述

我有一个简单的Postgres测试表，其中包含id，带有时区的时间戳.下面的测试和输出应该是不言自明的，但是总而言之，我插入了带有-6偏移量时间戳的行.它已正确插入数据库，然后在同一小时从数据库中装入，但偏移量错误，特别是Z而不是-6.

I have a simple Postgres test table of id, timestamp with timezone. The test and output below should be self explanatory, but to summarize, I insert a row that has a timestamp with a -6 offset. It is properly inserted into the database, then loaded out of the database with the same hour, but the wrong offset, specifically Z instead of -6.

我尝试将数据库设置为UTC，当我在命令行手动选择时，它会正确显示UTC时间.将数据库设置为山，它以-6的偏移量显示预期时间.

I've tried setting my database to UTC, and when I manually select at the command line, it properly shows the time in UTC. Set database to mountain, it shows the expected time with an offset of -6.

通过在jOOQ中执行的语句将数据库强制到各个时区似乎没有任何作用.

Forcing the database to various timezones via a statement executed in jOOQ seems to do nothing.

context.execute( "set timezone TO 'GMT';" ); // this has no effect

强制我的系统时间使用UTC可以有效解决此问题，但是由于许多原因，这是无法接受的.

Forcing my system time to UTC effectively works around the problem, but is for many reasons unacceptable.

TimeZone.setDefault( TimeZone.getTimeZone( "UTC" ) ); // this is a band aid that works, but is not sustainable

这是单元测试:

@Test
public void confirmDateRoundTripFromDb() throws SQLException, DatatypeConfigurationException
{
    ZonedDateTime testDate = ZonedDateTime.of( 2019, 05, 30, 12, 54, 32, 203, TimeUtilities.CENTRAL_ZONEID );

    final OffsetDateTime testDateAsOffset = testDate.toOffsetDateTime( );

    try( PGConnection dbConnection = DatabaseUtility.getPostgresConnection( _unitTestConfig.getSection("Postgres").getProperties(), _testDbName ) )
    {
        DSLContext context = DSL.using( dbConnection, SQLDialect.POSTGRES );
        DateTestsRecord dateTestsRecord = context.newRecord( DATE_TESTS );
        dateTestsRecord.setTestTimestamp( testDateAsOffset );
        dateTestsRecord.store();

        int id = dateTestsRecord.getId();

        DateTestsRecord insertedRecord = context.selectFrom( DATE_TESTS ).where( DATE_TESTS.ID.eq( id ) ).fetchAny();
        System.out.println( testDateAsOffset );
        System.out.println( insertedRecord.getTestTimestamp() );
    }
}

输出:

2019-05-30T12:54:32.000000203-05:00
2019-05-30T11:54:32Z

有趣的是，如果我在中部添加一个日期，则小时已正确更改为山峰，但是往返后的输出仍然只是愉快地报告了Z.

Interestingly, if I add a date in central, the hour is correctly changed to mountain, but the output after the round trip still just happily reports Z.

我认为这不是预期的吗?难道我做错了什么?如果没有，那么任何适用于全球的变通办法的想法都可以吗?有很多开发人员，每次选择时，我都不会因为必须使用一些特殊的逻辑来处理日期而感到兴奋，这似乎很脆弱.

I assume this is not expected? Am I doing something wrong? If not, any ideas for a workaround that would apply globally? There are several developers on this, I'm not excited about having to handle the date with some special logic every time we select, seems fragile.

我一直在运行3.10，但刚刚升级到3.12，结果相同.

I've been running 3.10, but just upgraded to 3.12 with the same results.

推荐答案

impossibl pgjdbc驱动程序版本0.7.1与jOOQ之间不兼容，导致从Postgres数据库中选择带有时区的时间戳时不应用偏移量.

There is an incompatibility between the impossibl pgjdbc driver version 0.7.1 and jOOQ causing offsets not to be applied when selecting timestamp with timezone back out of a postgres database.

万一其他人也正在运行这个jar组合，如果可能的驱动程序中的功能是必需的，建议更新到pgjdbc 0.8.2，否则请放弃.

In the unlikely event that someone else is also running this combination of jars, recommend updating to pgjdbc 0.8.2 if the features in the impossibl driver are necessary or abandoning if not.

这篇关于jOOQ返回偏移日期时间为Z(UTC)，即使不是的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！