How to set correct MySQL JDBC timezone in Spring Boot configuration

Question

DB:

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for osx10.10 (x86_64) using  EditLine wrapper

Spring Boot: 2.1.1.RELEASE

The error:

2019-01-01 15:56:25.849 ERROR 39957 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.
> :bootRun
java.sql.SQLException: The server time zone value 'AEDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Relevant parts of my properties file:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&serverTimezone=UTC
spring.datasource.username=#####
spring.datasource.password=########
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

What I find odd about this is that the error indicates that the timezone being used it AEDT, and yet I specified UTC in the spring.datasource.url. Does Hikari read something else when it initializes?

It does look very much like Hikari ignores the server timezone setting in the database url in favour of using my own machine's timezone which happens to be 'AEDT' (Melbourne, Australia) - This is unwanted behaviour. I would like Hikari to ignore my own machine's timezone. Does anyone know how to make it do that?

GolamMazid Sajib · Answer 1 · 2020-05-06T09:54:04.073

18

Set useLegacyDatetimeCode false and set ServerTimezone.

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false

edited May 06 '20 at 09:54

answered Jan 01 '19 at 06:44

GolamMazid Sajib

8,698
6
21
39

thanks, but I tried that already - no effect whatsoever. As I mentioned, I don't think Hikari is looking at the connection string for timezone settings. Besides, I think the `useLegacyDatetimeCode` workaround is mostly for Hibernate (which I'm not using) – Michael Coxon Jan 01 '19 at 06:58
I am using mysql 8.0.16, cannot start my spring boot app complaining time zone, so I tried the above settings and the app started. – Janet May 08 '19 at 04:18

score 15 · Accepted Answer · edited Feb 06 '22 at 18:56

Thanks for your answers, but I have found the solution.

As I suspected, Hikari ignores whatever you put in the datasource URL (so sorry guys, it doesn't matter what you chuck in there), essentially, it reads the timezone setting from MySQL itself, i.e., whatever the result you see when issuing the command:

SELECT @@GLOBAL.time_zone;

in MySQL. In my case, the result was "SYSTEM", which is whatever my local machine it set at. This was AEDT, which is not supported by the MySQL driver and hence my exception.

Running this same query in AWS yielded the value "UTC", which is supported (and, actually what I wanted).

Therefore, I had to set the timezone in my local MySQL server.

Firstly, I had to load the available timezones from my host (Mac OS X) into MySQL. I had to find out where the zoneinfo file was (/usr/share/zoneinfo in my case) then find out out where the `mysql_tzinfo_to_sql' utility was (bin directory of the MySQL installation) and use it to load my local machine's supported timezones. In Mac OS X, I ended up running the command:

/usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Then in MySQL I could run the command:

SET GLOBAL time_zone = UTC;

this is a valid timezone, and is synchronized with the cloud based instances.

I think this is a real trap for a lot of people using MySQL with Spring Boot. It will work while people are in supported timezones, but if your development machine should switch to an unsupported timezone, it will quite mysteriously break, I'm surprised that it isn't documented anywhere. The source code of the MySQL Connector/J makes it obvious, but you wouldn't know it otherwise.

Maybe its because MySQL is just so 5 years ago, and I'm an old fossil and, and, well, just get off my lawn!

score 14 · Answer 3 · answered Jun 11 '19 at 06:09

14

This worked for me. I set the variable in the db URL as such in application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/db_name?serverTimezone=America/Los_Angeles

answered Jun 11 '19 at 06:09

Shant Dashjian

888
11
20

1

This is the one that worked for me. Never thought of using that format for specifying the timezone! I used `Australia/Melbourne` – Jase Nov 14 '19 at 13:00
1

Great answer, if any one would like to use Zero Time Zone, set the value to `serverTimezone=Etc/UTC` – Ron Shoshani Sep 08 '20 at 09:10

score 6 · Answer 4 · edited Apr 12 '19 at 16:47

6

I added the following variable in the db url under application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/db_name?serverTimezone=GMT-6

and Spring Boot now runs ok.

edited Apr 12 '19 at 16:47

Imran

5,542
3
23
46

answered Apr 12 '19 at 16:14

M. Lopez

61
1
2
3

Wilson Barbosa · Answer 5 · 2020-11-27T06:27:51.657

5

I'm using MySQL 8 and solved this question by injecting the value of the user.timezone property.

Using Java 8 in my project.

Relevant settings in appliction.properties:

spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.datasource.url=jdbc:mysql://localhost:3306/mudi?serverTimezone=${user.timezone}

Using 2.3.4.RELEASE as parent project.

Dependencies used:

spring-boot-starter-thymeleaf
spring-boot-starter-web
spring-boot-devtools
spring-boot-starter-test
junit-vintage-engine
spring-boot-starter-data-jpa
mysql-connector-java

HTH,

WB::

edited Nov 27 '20 at 06:27

answered Oct 03 '20 at 05:45

Wilson Barbosa

86
1
2

1

great stuff... out of all these answers i like this one best. – Llama Nov 26 '20 at 02:18

score 3 · Answer 6 · answered Sep 29 '19 at 13:16

3

Following Shaun it was enough for me with this: spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/DEMOS?serverTimezone=UTC

answered Sep 29 '19 at 13:16

Dimitri Flores

31
3

score 2 · Answer 7 · answered Jun 15 '19 at 03:14

2

Set useLegacyDatetimeCode false is working for me. Thanks..

spring.datasource.url: jdbc:mysql://localhost:3306/employee_directory? 
useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false

answered Jun 15 '19 at 03:14

Numery

402
1
6
16

score 2 · Answer 8 · answered May 27 '20 at 18:05

2

Using jdk 14, I had to do this to my application.properties

spring.jpa.properties.hibername.jdbc.time_zone=US/Michigan

edit: formatting

answered May 27 '20 at 18:05

lordgasmic

21
2

It solved the issue in the Spring boot version 2.1.6.RELEASE – ankit.vishen May 09 '22 at 11:19

score 2 · Answer 9 · answered Aug 06 '20 at 04:43

I've run into the same issue but adding

?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false

after your schema name fixed this issue for me. Should look something like this

spring.datasource.url = jdbc:mysql://localhost:3306/my-schema-name?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false

I'm currently using MySQL v8 and this works well for me.

score 1 · Answer 10 · edited Jan 16 '20 at 17:50

1

Another way is to put into the file application.properties the next line:

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

It comes as an option in: https://www.baeldung.com/mysql-jdbc-timezone-spring-boot

edited Jan 16 '20 at 17:50

Das_Geek

2,775
7
20
26

answered Jan 16 '20 at 15:31

MayEncoding

87
1
12

score 0 · Answer 11 · answered Jan 01 '19 at 09:23

Add useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false to your connection string:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/avmaint-local?useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

score 0 · Answer 12 · answered Jun 17 '21 at 08:50

I tried 3 methods.

TimeZone.setDefault(TimeZone.getTimeZone("Asia/Colombo")); // In Spring Initializer
spring.jpa.properties.hibernate.jdbc.time_zone=Asia/Colombo // In applications.properties
<spring-boot.run.jvmArguments>-Duser.timezone=Asia/Colombo</spring-boot.run.jvmArguments> // Setting property in POM file

I also tried setting properties through JDBC URL;

useLegacyDatetimeCode=false
serverTimezone=Asia/Colombo

Non of the above worked for me. I noticed that the most unlikely suspect Jackson was overriding my Timezone.

spring.jackson.time-zone=Asia/Colombo (The Answer)

Include this in you application.properties (it alone is enough)

Tested on Spring boot - 2.1.5.RELEASE , MYSQL8.

How to set correct MySQL JDBC timezone in Spring Boot configuration

12 Answers12

Linked