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

How to change the domestic source of Ubuntu 20.04 apt

UPD 2020.2.26 Currently Ubuntu 20.04 LTS has not ...

Summary of Vue's cross-domain problem handling and solutions

When you send a network request, the following sa...

Common pitfalls of using React Hooks

React Hooks is a new feature introduced in React ...

How to extract string elements from non-fixed positions in MySQL

Preface Note: The test database version is MySQL ...

How to configure Nginx virtual host in CentOS 7.3

Experimental environment A minimally installed Ce...

Implementing a simple student information management system based on VUE

Table of contents 1. Main functions 2. Implementa...

Native JS encapsulation vue Tab switching effect

This article example shares the specific code of ...

Use shell script to install python3.8 environment in CentOS7 (recommended)

One-click execution To install Python 3.8 in a vi...

js implementation of verification code case

This article example shares the specific code of ...

MySQL master-slave data is inconsistent, prompt: Slave_SQL_Running: No solution

This article uses an example to describe the solu...

Detailed explanation of virtual DOM in Vue source code analysis

Why do we need virtual dom? Virtual DOM is design...

Two ways to manage volumes in Docker

In the previous article, I introduced the basic k...

MySQL joint table query basic operation left-join common pitfalls

Overview For small and medium-sized projects, joi...