mysql-jdbc-timezone-spring-boot

Posted on: 2019-09-29 2019-11-19
Tags: Persistence, Spring Boot, Spring Data

Setting the MySQL JDBC Timezone Using Spring Boot Configuration

1. Overview

Sometimes, when we’re storing dates in MySQL, we realize that the date from the database is different from our system or JVM.

Other times, we just need to run our app with another timezone.

In this tutorial, we’re going to see different ways to change the timezone of MySQL using Spring Boot configuration.

2. Timezone as a URL Param

One way we can specify the timezone is in the connection URL string as a parameter.

By default, MySQL uses useLegacyDatetimeCode=true. In order to select our timezone, we have to change this property to false. And of course, we also add the property serverTimezone to specify the timezone:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useLegacyDatetimeCode=false
    username: root
    password:

Also, we can, of course, configure the datasource with Java configuration instead.

We have more information about this property and others in the MySQL official documentation.

3. Spring Boot Property

Or, instead of indicating the timezone via the serverTimezone URL parameter, we can specify the time_zone property in our Spring Boot configuration:

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

Or with YAML:

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          time_zone: UTC

But, it’s still necessary to add useLegacyDatetimeCode=false in the URL as we’ve seen before.

4. JVM Default Timezone

And of course, we can update the default timezone that Java has.

Again, we add useLegacyDatetimeCode=false in the URL as before. And then we just need to add a simple method:

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

But, this solution could generate other problems since it’s application-wide. Perhaps other parts of the applications need another timezone. For example, we may need to connect to different databases and they, for some reason, need dates to be stored in different timezones.

5. Conclusion

In this tutorial, we saw a few different ways to configure the MySQL JDBC timezone in Spring. We did it with a URL param, with a property, and by changing the JVM default timezone.

As always, the full set of examples is over on GitHub.

getdocs

3043