phpmyadmin Question posted in
The official documentation can be found here.

Phpmyadmin – Hibernate TimeZone Normalization Issue: Incorrect Offset for Specific DateTime Entries in MySQL

I have a strange problem with MySQL Hibernate, i’m trying to use an existing database with a new Spring Boot application, the columns storing dates are of type DateTime.

Here’s what I did:

Specified the timezone in my application.
Added the annotation @TimeZoneStorage(TimeZoneStorageType.NORMALIZE) to the OffsetDateTime fields in my classes, this ensures that Hibernate adds the JVM’s offset when reading and normalizes to the JVM’s timezone when saving, without this, Hibernate defaults to normalizing dates to UTC.

For most of the DateTime entries, this approach works correctly, however, for some specific dates, the offset when reading is always +1 hour over the current JVM timezone, even within the same table, if I change the value using phpMyAdmin, it gets corrected and matches the others.

I’m confused about where the information about the added offset could be stored and why changing it with phpMyAdmin causes it to revert back to the correct offset.

Has anyone encountered a similar issue or have insights into why this might be happening?

Tags:

2

Answers


  1. 0

    You can handle the timezone by forcing it in the jvm params in your main like this

    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

    Otherwise you can manage your hibernate properties and define them in a global way in the application.yml file

    hibernate:
  jdbc:
    time_zone: UTC
    timezone:
      default_storage: NORMALIZE

    I suggest you check the hibernate 6 migration guide for more details

    Login or Signup to reply.
  2. 0

    DATETIME is wrong type

    The DATETIME type in MySQL represents merely a date and a time, lacking the context of a time zone or offset-from-UTC. See the manual. This type is akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE. In JDBC 4.2+, this type maps to the Java class Java.time.LocalDateTime.

    Saying “noon on January 23rd of 2025” is inherently ambiguous. We cannot nail down a moment, a point on the timeline, from merely that date and time. We have no idea if that means noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US — three very different moments, several hours apart.

    Adding annotations and using OffsetDateTime with that wrong type is just making a hopeless mess.

    TIMESTAMP is right type

    For a moment, you must define a column of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE. In MySQL, that would be TIMESTAMP. In conjunction with that type, you can use the Java class java.time.OffsetDateTime as mapped in JDBC 4.2 and later.

    After retrieving a OffsetDateTime object, you can apply a ZoneId to get a ZonedDateTime.

    I’ve written on this topic many many times already on Stack Overflow, as have others. Search to learn more.

    Unfortunately, the MySQL type TIMESTAMP cannot hold values past the year 2038. If that is a problem for your app, you’ll need to switch to another database engine that has more robust date-time support, such as PostgreSQL, H2, Microsoft SQL Server, Oracle, etc.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search