MySQL data statistics error due to server time and time zone errors

Operation over there, the background to obtain data, time is inaccurate, immediately find operations, the program side also give me this side provides a clue, is in MySQL inside executed

SELECT From_unixtime (1476883657);

The time displayed is not Beijing time. Because recently moved MySQL to Hong Kong, need to follow the Beijing time to set up the server time.

Look at the server's system time first.

Datethu Oct 13:54:12 EDT 2016

Time is indeed incorrect, set the system time

Date-s "2016-10-20 14:41:31"

Write CMOS

Clock-w

Think this is done, and then look in the database,

>select From_unixtime (1476883657);

The time shown is still wrong,

>select now ();

Check the current time of the database is also incorrect, this is why

Later looked intently, the original time zone has a problem. Then we need to modify the system time zone

Cp/usr/share/zoneinfo/asia/shanghai/etc/localtimecp:overwrite '/etc/localtime '? Y

Look at the server again.

Datethu Oct 13:54:12 CST 2016

OK, the time zone becomes CST instead of the previous EDT; now that the server's system time and time zone are right, go to MySQL and check to see if the current time and timestamp are displayed correctly or not.

View current time:

>select now (); or >select sysdate ()

>select From_unixtime (1476883657);

There is still a problem, then analysis, it should be the time zone problem of MySQL

>show variables like "%time_zone%"; +------------------+--------+| variable_name | Value |+------------------+--------+| System_time_zone | EDT | | Time_zone | SYSTEM |+------------------+--------+2 rows in Set (0.00 sec)

#time_zone说明mysql使用system的时区, System_time_zone Description system uses the EDT time zone

As expected, the MySQL time zone has not been changed, indicating that MySQL is using the previous system time zone, need to reboot to solve this problem, when the online business can easily restart, then use a temporary method.

> Set global time_zone = ';  # #修改mysql全局时区为北京时间, where we are located in the East 8 district > Set time_zone = ' ";  # #修改当前会话时区 > Flush privileges; #立即生效

Well, look at the MySQL time zone problem.

>show variables like "%time_zone%"; +------------------+--------+| variable_name | Value |+------------------+--------+| System_time_zone | EDT | | Time_zone | +08:00 |+------------------+--------+2 rows in Set (0.00 sec)

Try the time stamp again, OK, because the database is stored in time stamp, so the time of the MySQL time zone and the system is modified, and time zone solves the problem smoothly.

It is worth considering that, because the system's time zone has been modified and time has been modified, and the CMOS is written, even if the server restarts, the time and timezone are correct, and MySQL needs to restart to read to the latest time zone of the system, since the system time zone and time are correct, Then MySQL is correct even if the time zone is restarted. This is also a permanent practice.

This article is from the "Zheng Xiaoming Technology Blog" blog, make sure to keep this source http://zhengmingjing.blog.51cto.com/1587142/1863930

MySQL data statistics error due to server time and time zone errors