Hibernate OffsetTime and OffsetDateTime time inconsistency

Question

I have noticed we are experiencing inconsistencies with OffsetTime and OffsetDateTime with MySQL serverTimezone set to UTC.

I have demonstrated this by having an entity that has an OffsetTime field and an OffsetDateTime field. For some reason OffsetTime is being converted to having +1 hours to get the UTC time, although the database is set to UTC. OffsetDateTime, which has the same time, except has a date aswell, returns the correct time.

private OffsetTime offsetTime;
private OffsetDateTime offsetDateTime;

Database shows these fields with these values:

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

Answer 1

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.