Steps to solve the MySQL 8.0 time zone problem

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

Recommend

Steps for packaging and configuring SVG components in Vue projects

I just joined a new company recently. After getti...

Nginx configuration file detailed explanation and optimization suggestions guide

Table of contents 1. Overview 2. nginx.conf 1) Co...

Vue imports Echarts to realize line scatter chart

This article shares the specific code of Vue impo...

MySQL statement execution order and writing order example analysis

The complete syntax of the select statement is: S...

...

HTML table tag tutorial (21): row border color attribute BORDERCOLOR

To beautify the table, you can set different bord...

About ROS2 installation and docker environment usage

Table of contents Why use Docker? Docker installa...

Detailed analysis of replication in Mysql

1.MySQL replication concept It means transferring...

JS deep and shallow copy details

Table of contents 1. What does shallow copy mean?...

The difference between delete, truncate, and drop and how to choose

Preface Last week, a colleague asked me: "Br...

Implementation example of JS native double-column shuttle selection box

Table of contents When to use Structural branches...

Analyzing the node event loop and message queue

Table of contents What is async? Why do we need a...

Top 10 Js Image Processing Libraries

Table of contents introduce 1. Pica 2. Lena.js 3....

Detailed explanation of the steps to create a web server with node.js

Preface It is very simple to create a server in n...

A simple method to be compatible with IE6's min-width and min-height

If a website is widescreen, you drag the browser ...