Hibernate OffsetTime and OffsetDateTime time inconsistency-CodePudding

This

When the entity is returned from database, the OffsetTime returned is 10:00:00 (incorrect), however OffsetDateTime is set to "2022-12-22T09:00:00Z" (correct). Example of JSON return below:

What is happening here?

Thanks

Edit: We have no TIMESTAMP column, SELECT @@global.time_zone, @@session.time_zone; in mysql returns 00:00 for both values, another test here. Storing the value in the database using

setOffsetTime(LocalTime.of(9, 0).atOffset(ZoneOffset.UTC));

Printing this before storing using getOffsetTime().toString() returns '09:00Z' however this is stored as 08:00:00.... - one line below I store the datetime using

setOffsetDateTime(LocalDateTime.of(LocalDate.now(), LocalTime.of(9, 0)).atOffset(ZoneOffset.UTC));

and this returns '2022-12-26T09:00Z' despite being stored as '2022-12-26 09:00:00.000000' in the db which is correct.. notice the inconsistency between OffsetTime and OffsetDateTime

CodePudding user response：

I am not sure, but this looks like it could be related to how your database handles timezones and offsets when storing and retrieving values.

Have you checked if you store a value in a TIMESTAMP column, if MySQL converts the value from the current time zone to UTC for storage, and then converts it back to the current time zone when you retrieve the value?

For example if your MySQL server's time zone is set to UTC, and you are storing an OffsetTime value with an offset of 01:00, then MySQL will convert this value to UTC before storing it in the database. When you retrieve the value, it will be converted back to the server's time zone, which is UTC, so the offset will be removed and the value will be returned as 09:00:00. And if you store an OffsetDateTime value with the same offset, the value will be stored as-is in the database and will be returned with the same offset when you retrieve it. This is because OffsetDateTime includes both a date and a time, and the offset is part of the time information.

That would explain the inconsistency IMHO.

CodePudding user response：

After researching further I found a similar post which explains why OffsetTime is being calculated incorrectly

TLDR; I'm not sure why OffsetDateTime works correctly, but this explains why OffsetTime is being converted incorrectly - in 1970 my JVM timezone (Europe/London) was 01:00 all year round. Currently it is in GMT timezone which is 00:00, hence the inconsistency.

Please view the linked post for more info.

To fix, I forced the JVM to UTC timezone using TimeZone.setDefault(TimeZone.getTimeZone("UTC")); on start