Steps to solve the MySQL 8.0 time zone problem

Software Version

Windows: Windows 10
MySQL: mysql-8.0.16-winx64.zip

Problem Description

When java saves the current time to MySQL database, the time in MySQL is incorrect

Problem Analysis

Reason 1: Java database connection uses the UTC time zone (universal time), that is, serverTimezone=UTC

url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true

Reason 2: The time_zone attribute used by MySQL is +00:00, and Beijing time is 8 hours earlier than UTC time, that is, UTC+08:00

Check MySQL

/**
 * You can view the global time zone settings and session time zone settings * global.time_zone is responsible for the time zone setting when java connects to the database * session.time_zone is responsible for the time zone setting when the Navicat client connects to the database * /
mysql> select @@global.time_zone,@@session.time_zone;

/**
 * There is another method * System represents the use of system time zone * CST is a very messy time zone, it includes 4 time zones * /
mysql> show variables like '%time_zone%'; 

// Verify the time zone. This command line can detect whether session.time_zone is configured correctlymysql> select now();

Workaround

Step 1: Change the time zone in Java to East 8

// serverTimezone can be set to Beijing time GMT%, Shanghai time Asia/Shanghai or Hongkong time Hongkong
url: jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true

Step 2: Change the time zone of the MySQL database to East 8

// Method 1: Use command (Advantage: No need to restart MySQL service, Disadvantage: Once MySQL service is restarted, the settings will disappear)
mysql> set time_zone = '+8:00';
mysql> set global time_zone = '+8:00';
// Method 2: Modify the my.ini configuration file (Advantage: Permanently save settings, Disadvantage: Need to restart the MySQL service)
[mysqld]
// Set the default time zone default-time_zone='+8:00'

This is the end of this article about the steps to solve the MySQL 8.0 time zone problem. For more relevant MySQL 8.0 time zone content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

mysql solves time zone related problems
Detailed explanation of mysql time zone problem in Java
How to view and modify the time zone in MySQL
Summary of methods for modifying MySQL time zone
How to distinguish uppercase and lowercase letters in strings when querying MySQL
mysql time zone problem

<<: How to create your own Docker image and upload it to Dockerhub

>>: XHTML language default CSS style