Simple test inserting a serial id and a timestamp with timezone then selecting the data back. The value returned is in my local time but is indicated as UTC. Below are raw selects from Postgres and corresponding values in Java as queried via jOOQ.
set timezone to 'UTC';
select * from date_tests;
id | test_timestamp
----+------------------------
1 | 2019-05-30 17:54:32+00
set timezone to 'America/Denver';
SET
select * from date_tests;
id | test_timestamp
----+------------------------
1 | 2019-05-30 11:54:32-06
When querying Postgres, it tells me what time zone the Instant has been converted to. When I get the same responses in Java, the Instant is converted to whatever time the JVM is set to, but the offset is NOT set accordingly. Example below. I believe I would classify this as a jOOQ bug.
TimeZone.setDefault( TimeZone.getTimeZone( "UTC" ) );
DateTestsRecord insertedRecord = context.selectFrom( DATE_TESTS ).where( DATE_TESTS.ID.eq( id ) ).fetchAny();
System.out.println( insertedRecord.getTestTimestamp() );
//Output is 2019-05-30T17:54:32Z
//Makes sense, the inserted timestamp was hour 17 UTC
TimeZone.setDefault( TimeZone.getTimeZone( "America/Denver" ));
DateTestsRecord insertedRecordInMountain = context.selectFrom( DATE_TESTS ).where( DATE_TESTS.ID.eq( id ) ).fetchAny();
System.out.println( insertedRecordInMountain.getTestTimestamp() );
//Output is 2019-05-30T11:54:32Z
//Makes NO sense, the hour was 17 UTC, either give it to me as 17 Z
//Or convert to -6 because my system time is Mountain,
//but indicate it has a -6 offset.
//But don't give a -6 time and say it's Z.
Once again, this seems to have nothing to do with what time the database is set to, depends only on the default time set with TimeZone.setDefault.
Original test code and discussion at jOOQ returns offset date time as Z (UTC) even though it's not
More info after debugging:
After stepping through the jOOQ code for a while, I believe I found the culprit. In DefaultBinding.java:
else if (type == OffsetDateTime.class) {
result = (T) offsetDateTime(ctx.resultSet().getString(ctx.index()));
}
If I call getObject here, I find the object at index 2 (the test_timestamp) is a java.sql.Timestamp. The toString value of this object is the "2019-05-30 11:54:32.0" without the offset, but clearly with the offset applied (look at postgres output above, hour 11 is with a -6 offset). If I call getObject, I can see that the expected offset is stored, but not properly returned using the default toString. I'm on Java 1.8.0_211-b12. I don't know if this toString behavior has changed over time.
Continuing from there, I see the OffsetDateTime parse method comments indicate this string was expected to contain the offset, which seems obvious. No way to invent the correct offset if the string does not contain it, which it does not thanks to the Timestamp toString behavior I discuss above.
