8

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

nikiforovpizza
  • 487
  • 1
  • 7
  • 13
Ceryni
  • 371
  • 1
  • 5
  • 18
  • When retrieved via JPA, the value is correct or still off? – Wabi Nov 10 '17 at 19:24
  • its off in db and in the application thats why i am curious. I thought it might be because of different timezone settings thats why i set them the same on mysql server and in the application. But even then its off. – Ceryni Nov 10 '17 at 19:42
  • Hm... timezone of the actual machine (OS) on which it is running? I remember having this issue couple years back, unfortunately dont remember the details anymore. – Wabi Nov 10 '17 at 20:48
  • UTC both mysql and applicationserver – Ceryni Nov 10 '17 at 20:53
  • Does [this answer](https://stackoverflow.com/a/40438746/2144390) help any? – Gord Thompson Nov 11 '17 at 15:25
  • No i tried that as well but thank you – Ceryni Nov 11 '17 at 18:29
  • I'm having the same issue. Did you find a fix for it ? Thanks – db80 Apr 20 '18 at 08:26
  • i forgot about this. I had this issue again later in another project and it was due to the docker image i created having different time settings that the host. So make sure Host,docker images, and applications do have the same time settings – Ceryni May 10 '18 at 16:19
  • https://bugs.mysql.com/bug.php?id=85570 – Arun Chandrasekaran Nov 29 '18 at 21:21
  • Possibly related: https://bugs.mysql.com/bug.php?id=93444 – Gord Thompson May 16 '19 at 13:13
  • Having the same problem even with the following configurations: application properties: 1) spring.datasource.url=jdbc:mysql://xxxx/database?serverTimezone=UTC 2) spring.jpa.properties.hibernate.jdbc.time_zone=UTC Spring main class: @PostConstruct void started() { TimeZone.setDefault(TimeZone.getTimeZone("UTC")); } Spring Boot 2.5.8 (w/ hibernate 5.4.33 and mysql connector 8.0.27) and MySQL 8.0 – epol Jul 11 '22 at 22:04

2 Answers2

1

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

Naveen
  • 29
  • 4
0

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.

Koboo
  • 1