【转载】How to solve the problem that getting timestamp from Mysql database is 8 hours earlier than the normal time
This article introduces the relevant knowledge of "how to solve the problem of obtaining timestamp from Mysql database 8 hours earlier than the normal time". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and achieve something!
The problem is: using the same Mysql database to obtain the same time data, the return page under window is the normal time, but it is 8 hours earlier under linux.
Server environment: CentOS 8, mysql8.0.21
1, First, check the server time zone setting and the synchronization of system clock and hardware clock:
((I) date see/Set system time
1,Set the date to November 6, 2020
[root@centos7 ~]# date -s 11/06/20
2,Set the time to 11:12:13
[root@centos7 ~]# date -s 11:12:13
3,Set the time to 11:12:13 on November 6, 2020( MMDDhhmmYYYY.ss)
[root@centos7 ~]# date 1106111220.13
((II) hwclock/clock see/Set hardware time
1,View the system hardware clock (the following two have the same effect)
[root@centos7 ~]# hwclock --show
[root@centos7 ~]# clock --show
2,Set the hardware time (the following two have the same effect)
[root@centos7 ~]# Hwlock -- set -- date = "11 / 06 / 20 12:13" (month / day / year hour: minute: Second)
[root@centos7 ~]# clock --set --date="11/06/20 12:13" (month / day / year hour: minute: Second)
(3) Synchronization system and hardware clock
1,System time is synchronized with hardware time (the following two have the same effect)
[root@centos7 ~]# hwclock --hctosys
[root@centos7 ~]# clock --hctosys
remarks: hc Represents hardware time, sys Represents the system time. Based on the hardware time, the system time is synchronized with the hardware time
2,Hardware time is synchronized with system time (the following two have the same effect)
[root@centos7 ~]# hwclock --systohc
[root@centos7 ~]# clock --systohc
Note: Based on the system time, the hardware time is synchronized with the system time
(4) Modify time zone
#The time zone file of CentOS and Ubuntu is / etc/localtime, but after CentOS7, localtime and has become a linked file
[root@centos7 ~]# ll /etc/localtime
lrwxrwxrwx 1 root root 33 Nov 15 2020 /etc/localtime -> /usr/share/zoneinfo/Asia/Shanghai
# There are several ways to correct errors:
[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 #Find the full name of the Chinese time zone
[root@centos7 ~]# timedatectl set-timezone Asia/Shanghai #Other time zones, and so on
# Or create soft links directly and manually
[root@centos7 ~]# ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
2, If it is determined that there is a problem with the server, but it cannot be modified due to system region and other reasons, you can also set the time zone of mysql:
>select now();
+---------------------+
| now() |
+---------------------+
| 2020-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 the time zone of mysql system
system_time_zone description system uses EST time zone (PS: EST us time zone, CST world standard world)
#Temporary modification, executed in mysql
>set global time_zone = '+8:00'; ##Modify the mysql global time zone to Beijing time, that is, the East 8 area where we are located
>set time_zone = '+8:00'; ##Modify the current session time zone
>flush privileges; #Effective immediately
#Permanently modify and exit mysql execution
[root@centos7 ~]# vim /etc/my.cnf ##In the [mysqld] field, add
[root@centos7 ~]# default-time_zone = '+8:00'
[root@centos7 ~]# /etc/init.d/mysqld restart ##Restart mysql to make the new time zone take effect
3, If it is determined that there is a problem with the server, but the server and database cannot be modified due to objective factors, it can also be modified on the database request URL:
application.yml Configuration: (system database parameter configuration file, GMT%2B8 After this parameter is escaped GMT+8 Indicates that the database time is set to East Zone 8(Beijing)Time, if set GMT，Can be in Spring.jackson.time-zone Medium setting GMT+8，Just set one place)
datasource:
url: jdbc:mysql://localhost:3306/test-db?useUnicode=true&characterEncoding=UTF-8&useSSL=false&useTimezone=true&serverTimezone=GMT%2B8
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
4, There are also configurations with larger brain holes. Notes should be made for each value, which is easy to be omitted:
# Set on the Date of the entity class Po class to receive the time field in the database:
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
After a wave of changes, return to the topic: Docker container time is inconsistent with the host time, and Docker is making trouble again!
# Host time
[root@centos8]# date
Mon Nov 23 13:43:52 CST 2020
# Container time
[root@centos8]# docker exec e8573a89fb94 date
Mon Nov 23 05:44:39 UTC 2020
CST should mean (China Shanghai Time)
UTC should mean Coordinated Universal Time
Therefore, the difference between these two times should actually be 8 hours. (PS: therefore, for containers that have not been set, the time difference between them and the host computer is generally 8h). The time zones of the two must be unified.
# localtime of shared host (method 1)
# When creating a container, specify the startup parameters and mount the localtime file into the container to ensure that the time zones used by the two are consistent.
[root@centos8]# docker run --name <name> -v /etc/localtime:/etc/localtime:ro
# Copy the localtime of the host (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 of obtaining timestamp from Mysql database 8 hours earlier than the normal time" is introduced here. Thank you for reading! Xiaobian will output more high-quality practical articles for you!
