SpringBoot mysql time zone problem

Find the reason

Several time zone settings common in back-end development

The first set point configuration file spring.jackson.time-zone

The second setting point, the higher version of SpringBoot version, mysql-connector-java uses 8.X. The jdbc of mysql8.X has been upgraded, and the time zone (serverTimezone) attribute has been added, and it is not allowed to be empty.

The third set point mysql time_zone variable

Word meaning

serverTimezone temporarily specifies the time zone of the mysql server

spring.jackson.time-zone set spring default time zone

system_time_zone MySQL server time zone, time_zone defaults to System following system_time_zone

Several situations

1. Time_zone is System, serverTimezone is GMT+8, jackson.time-zone is not defined

Insertion situation

Query this record again

Personally, I feel that Spring’s default time zone is Greenwich time zone, and the current time zone of the web server is East Eight District.


2. set GLOBAL time_zone ='+3:00', serverTimezone is GMT+8, jackson.time-zone is GMT+8

createTime is timestamp type

After modifying the configuration, you need to restart SpringBoot

New situation

Display in the database

Search record

Personal understanding, the serverTimezone setting overwrites mysql's time_zone variable, and the time zone of the conversation with SpringBoot is still 8

3. In the above environment, without restarting SpringBoot, directly change time_zone ='+5:00'

After the change, the last record will be adjusted 2 hours later.

SpringBoot query, the same

Note that the timeStamp type stores Greenwich Mean Time, plus the time_zone time zone

When the time_zone changes, the session is not over, serverTimeZone 8 is still corresponding to time_zone 3

SpringBoot insert

I personally understand that serverTimeZone East 8 still corresponds to time_zone East 3, but the insert finds that the current time_zone has been changed to East 5, so add 2 hours.

Restart SpringBoot and re-query

Although the mysql variable time_zone is +5, after restarting, serverTimeZone is directly overwritten and the time zone is set to east 8.


Change the time_zone back to East 3 again

Changed back and reopened the table and found it was back again

Do not start SpringBoot, query data, still the same

At this time, add a piece of data.

Pushed forward for 2 hours.

SpringBoot query

Restart SpringBoot and find out that it is the data in the library.


4. ServerTimezone is GMT, jackson.time-zone is GMT+8, and time_zone is East 3.

serverTimeZone is Greenwich Mean Time, and the web server is Dongba, so it is directly postponed for 8 hours

Take it out just to reverse, the display is normal.

At this time, modify serverTimeZone to Dongba.


5. The time field type is timestamp, the default current_timestamp is used, serverTimezone is GMT, jackson.time-zone is GMT+8, and time_zone is East 3.

Because the mysql time zone Dongsan time is

The data after insertion is

But serverTimeZone is Greenwich Mean Time, jackson.time-zone is Dongba, plus 8 hours


6. The time field type is datetime, serverTimezone is GMT+8, jackson.time-zone is GMT+8, and time_zone is east 3.

insert

In the library

Inquire

time_zone is modified from East 3 to East 5

Reopen library

Does not start SpringBoot

Restart SpringBoot, it's still the same.

Modify serverTimeZone to GMT, the others remain unchanged

Inquire

to sum up:

jackson.time-zone manages the time zone converted from the front end to the web server.

If the time type is datetime, serverTimeZone has the final say.

If the time type is timestamp

1. When the time_zone and serverTimeZone are not uniform, the time data in the table during the session is set according to the serverTimeZone

2. If the time_zone variable is modified during the process, the data in the library will be changed directly. However, the current session query will not be affected, and if new data is added, the actual storage will be adjusted according to the time_zone adjustment.

3. SpringBoot restart is actually a new session. The situation is the same as above_1.