3

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: enter image description here

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:

enter image description here

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

Tom
  • 215
  • 1
  • 10

2 Answers2

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.

lapadets
  • 1,067
  • 10
  • 38
  • Very strange, no TIMESTAMP column, `SELECT @@global.time_zone, @@session.time_zone;` in mysql returns +00:00 for both values, 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 – Tom Dec 26 '22 at 21:18
  • What's the timezone on your machine that is saving the data? – siggemannen Jan 02 '23 at 22:35
  • @siggemannen Europe/London, currently GMT so +00:00. See accepted answer, this explains the OffsetTime inaccuracy – Tom Jan 03 '23 at 18:08
1

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

Tom
  • 215
  • 1
  • 10