CODEWITHSUNDEEP
mysqlhibernatejpajdbclocaldate
Ceryni
Ceryni

Reputation: 441

Spring jpa hibernate mysql LocalDate off one day after persist

Whenever I persist LocalDate to MySQL Database, the Date is stored one day off (11 Nov 2017 becomes 10 Nov 2017). I've already tried to set the timezone in the application on MySQL server and set the legacyDateTimeCode to false but the problem still exists. Any idea on how to fix it? If I switch to local h2 Database, the Date is stored correctly.

Spring-boot-starter-parent: 1.5.7 hibernate 5.2.10 mysql: 5.7 LocalDate stored in DATE field

Upvotes: 10

Views: 3632

Answers (3)

Bekzod
Bekzod

Reputation: 131

Thanks for @Koboo's response. My code worked with his solution. But I found other simpler solution. You need just change java time zone inside bootstrap class's main method:

@SpringBootApplication
public class ApiApplication {

    public static void main(String[] args) {
        TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC));
        SpringApplication.run(ReflectBusinessApiApplication.class, args);
    }

}

Upvotes: 0

Koboo
Koboo

Reputation: 21

I have the following environment:

  • Spring-Boot (v3.0.6)
  • Spring (v6.0.8)
  • Spring-Data (v3.0.5)
  • Hibernate (v6.1.7.Final)
  • MySQL Connector J (v8.0.33)

I saved a LocalDate in UTC timezone without any problems, but if I wanted to read the LocalDate from the database it always shifted one day off (my machine TimeZone is Europe/Berlin). Maybe someone has the opposite problem, so that their LocalDate is shifted one day on.

Most of the previous answers couldn't really help me. The only solution, which I did not want to use, because it was impractical, was the following start parameter -Duser.timezone=UTC.

1. Change TimeZone.getDefault()

I tried to change the default TimeZone via a simple @PostConstruct method in Spring-Boot in my @SpringBootApplication.

@PostConstruct
public void changeTimezone() {
  TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

Still one day off.

2. TimeZone in MySQL Connection String

I tried to set the serverTimezone via mysql connection string with the following parameter:

jdbc:mysql://{host}/{database}?serverTimezone=UTC

Still one day off.

3. Tell Hibernate the TimeZone

Next thing is to tell hibernate which TimeZone I wanted to use. I've done that via hibernate properties and passed them into my LocalContainerEntityManagerFactoryBean instead of setting them into my application.properties.

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Surprise... Still one day off..

(Note: I also added the TimeZoneStorageType.NORMALIZE_UTC of hibernate 6, but it shouldn't effect the LocalDate java or java.sql.Date type.)

spring.jpa.properties.hibernate.timezone.default_storage=NORMALIZE_UTC

(HIBERNATE 6 ONLY)

4. Create debug hibernate type and check whats going on.

I debugged through a custom created hibernate descriptor type for LocalDate. It quickly became clear that hibernate was doing everything right and the "error" must lie deeper. So I jumped into mysql-connector-j source code.

The implementation of java.sql.ResultSet ResultSetImpl in the mysql-connector-j is parsing the java.sql.Date values with the TimeZone of the NativeServerSession.

See this method in ResultSetImpl

It uses the ServerSession#getDefaultTimeZone() method to retrieve the "wanted" TimeZone.

See the getDefaultTimeZone() method

As you can see, it checks if it should return the "cached" TimeZone defaultTimeZone field. If not, it recalls the TimeZone.getDefault() method.

Now the problem with Spring-Boot is, that the ServerSession object is already constructed and has cached the previous TimeZone.getDefault() (default jvm TimeZone) in the field linked above, before you can change the TimeZone in a @PostConstruct method via Spring like I've tried.

To fix the issue you can just add these parameters to the mysql connection string:

  • serverTimezone=UTC - Sets the TimeZone
  • useLegacyDatetimeCode=false - Disables the usage of legacy datetime codes
  • forceConnectionTimeZoneToSession=true - Forces the serverTimezone parameter to the session
  • cacheDefaultTimezone=false - Disables the caching TimeZone problem with spring-boot

Example connection string:

jdbc:mysql://{host}/{database}?serverTimezone=UTC&useLegacyDatetimeCode=false&forceConnectionTimeZoneToSession=true&cacheDefaultTimezone=false

I could not find any documentation for some parameters, so I link here again to the PropertyKey class where I found the parameters.

I hope I was able to help someone.

Upvotes: 2

Naveen
Naveen

Reputation: 29

Add spring.jpa.properties.hibernate.jdbc.time_zone : UTC In Properties file

Upvotes: 1

Related Questions