Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the problem of obtaining timestamp from Mysql database 8 hours earlier than normal time". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The problem is: using the same Mysql database to get the same time data, it is normal to return the page under window, but it is 8 hours earlier under linux.

Server environment: centos8,mysql8.0.21

First, check the server time zone setting and the synchronization of the system clock and the hardware clock:

(1) date view / set the system time 1, set the date to November 6, 2020 [root@centos7 ~] # date-s 11 date 06hand 202, set the time to 11:12:13 [root@centos7 ~] # date-s 1112 date 133, set the time to November 6, 2020 at 11:12:13 (MMDDhhmmYYYY.ss) # date 1106111220.13 (II) hwclock/clock View / set hardware time 1, Check the system hardware clock (the following two have the same effect) [root@centos7 ~] # hwclock-- show [root@centos7 ~] # clock-- show2, Set hardware time (the following two have the same effect) [root@centos7 ~] # hwclock-- set-- date= "12:13 on 11-06-20" (month / day / year: minutes: seconds) [root@centos7 ~] # clock-- set-- date= "12:13 on 11-06-20" (month / day / year: minutes: seconds) (III) synchronize the system and hardware clock 1. System time to find hardware time synchronization (the following two have the same effect) [root@centos7 ~] # hwclock-- hctosys [root@centos7 ~] # clock-- hctosys Note: hc represents hardware time Sys represents system time, based on hardware time, system time finds hardware time synchronization 2, hardware time finds system time synchronization (the following two have the same effect) [root@centos7 ~] # hwclock-- systohc [root@centos7 ~] # clock-- systohc remarks: take system time as the benchmark, hardware time finds system time synchronization (4) modify time zone # CentOS and Ubuntu time zone file is / etc/localtime But after CentOS7, localtime and become a link file [root@centos7 ~] # ll / etc/localtime lrwxrwxrwx 1 root root 33 Nov 15 2020 / etc/localtime-> / usr/share/zoneinfo/Asia/Shanghai#, if it is wrong, it needs to be modified. There are many ways: [root@centos7 ~] # cp / usr/share/zoneinfo/Asia/Shanghai / etc/localtime# the best way is to use the timedatectl command [root@centos7 ~] # timedatectl list-timezones | grep Shanghai # to find the full name of China's time zone [root@centos7 ~] # timedatectl set-timezone Asia/Shanghai # and so on # or directly create a soft link [root@centos7 ~] # ln-sf / usr/share/zoneinfo/Asia/Shanghai / etc/localtime

2. If you determine that there is a problem with the server but cannot modify it due to system region and other reasons, you can also set the time zone of mysql:

> select now (); +-+ | now () | +-+ | 0-11-23 12:30:06 | +-+ 1 row in set (0.00 sec) > show variables like "% time_zone%" +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | EST | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.00 sec)

Time_zone indicates that mysql uses the time zone of system

System_time_zone states that system uses the EST time zone (PS:EST US time zone, CST World Standard World)

# temporarily modify, execute > set global time_zone ='+ 8flush privileges in mysql; # # modify the global time zone of mysql to Beijing time, that is, our East Zone 8 > set time_zone ='+ 8virtual; # # modify the current session time zone > flush privileges # effective immediately # permanent modification, exit mysql and execute [root@centos7 ~] # vim / etc/my.cnf # # add [root@centos7 ~] # default-time_zone ='+ 8vim 00' [root@centos7 ~] # / etc/init.d/mysqld restart # # restart mysql to the [mysqld] area to make the new time zone effective

3. If you determine that there is a problem with the server, but the server and database cannot be modified due to objective factors, you can also modify it on the database request URL:

Application.yml configuration: (system database parameter configuration file. After the parameter GMT%2B8 is escaped, GMT+8 means to set the database time to East eighth District (Beijing) time. If you set GMT, you can set GMT+8 in Spring.jackson.time-zone. Just set it in one place) datasource: url: jdbc:mysql://localhost:3306/test-db?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useTimezone=true&serverTimezone=GMT%2B8spring: jackson: date-format: yyyy-MM-dd HH:mm:ss time-zone: GMT+8

Fourth, there is also a more ingenious configuration, each value should be annotated, it is easy to omit:

# set it on the Date of the entity class Po to receive the time field in the database: @ JsonFormat (pattern = "yyyy-MM-dd HH:mm:ss", timezone= "GMT+8")

After a wave of changes, get back to the point: the Docker container time is inconsistent with the host time, and Docker is messing around again!

# Host time [root@centos8] # dateMon Nov 23 13:43:52 CST 202 container time [root@centos8] # docker exec e8573a89fb94 dateMon Nov 23 05:44:39 UTC 2020

CST should mean (China Shanghai Time, East eighth District time)

UTC should mean (Coordinated Universal Time, standard time)

Therefore, the difference between the two times should actually be eight hours. (PS: therefore, for containers that have not been set, the time difference between the container and the host is usually 8 hours.) the time zone of the two must be unified.

# localtime of the shared host (method 1) # specify the startup parameters when creating the container, and mount the localtime file into the container to ensure that the time zone used by the two is the same. [root@centos8] # docker run-- name-v / etc/localtime:/etc/localtime:ro # copy the host's localtime (method 2) [root@centos8] # docker cp / etc/localtime [containerId]: / etc/localtime# create a custom dockerfile (method 3) [root@centos8] # RUN / bin/cp / usr/share/zoneinfo/Asia/Shanghai / etc/localtime\ & & echo 'Asia/Shanghai' > / etc/timezone\ "how to solve the problem from So much for the problem of Mysql database getting timestamp 8 hours earlier than normal. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.