教你如何修改mysql時(shí)區(qū)的問題

標(biāo)簽：

Java MySQL

前言

最近公司项目上实现一个新需求，本地已经测通了没问题，放到服务器上就出现毛病了，时间错了。

问题是什么？

需求是让统计24个小时（以当前时间为准向前推24个小时）的数据。SQL语句大概是这样：

SELECT
    h.ID,
    h.`NAME`,
    COALESCE (
        FORMAT(SUM(h.`VALUE`) / COUNT(1), 0),
        0
    ) `VALUE`,
    h.insert_time,
    DATE_SUB(NOW(), INTERVAL 23 HOUR)
FROM
    rep_health_data h
WHERE
    h.`NAME` = 'PM25'
AND h.insert_time >= DATE_FORMAT(
    DATE_SUB(NOW(), INTERVAL 23 HOUR),
    '%Y-%m-%d %k'
)
GROUP BY
    DATE_FORMAT(
        h.insert_time,
        '%Y-%m-%d %k'
    )
ORDER BY
    h.insert_time

预想结果应该为：

但实际SQL结果为：

多出来整整13个小时的数据。

为什么会这样？

一定是时区问题搞的鬼！查看mysql时区

# 查看数据库时区
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

查看服务器时间

root@Innodev0:/# date
Fri Mar 10 11:05:07 CST 2017
root@Innodev0:/# date -R
Fri, 10 Mar 2017 11:05:10 +0800

得，mysql默认使用的SYSTEM时区，即EST时区，查询相关资料可知，EST时区要比北京时间（东八区）慢13个小时，在数据库中的表现即为：

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-03-09 21:24:39 |
+---------------------+
1 row in set (0.00 sec)

确实比当前系统的时间慢了整整13个小时。

我应该怎么做？

一：通过sql命令临时修改

# 设置全局时区
mysql> set global time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
# 设置时区为东八区
mysql> set time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
# 刷新权限使设置立即生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

在查一次数据库时区

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

奥利给，东八区，没毛病！

然后执行sql

OK，已恢复正常！

二：修改my.cnf实现永久修改

vi /etc/mysql/my.cnf

然后在mysqld下边的配置中添加一行：

default-time_zone = '+8:00'

然后重启mysql

service mysql restart

三：代码控制：使用TimeZone工具类

关于TimeZone的具体使用方法不在本文范围内，如需了解请自行百度

import java.util.Date;
import java.util.Calendar;
import java.util.TimeZone;
import java.text.DateFormat;
public class TimeTest{
    public static void main(String[] args){
        Date date = new Date();
        System.out.println(date);
        System.out.println(System.getProperty("user.timezone"));
        System.out.println(System.getProperty("user.country"));

        // 获取所有时区，比如Etc/GMT+12, Etc/GMT+11, Pacific/Midway, Pacific/Niue, Pacific/Pago_Pago...
        //String[] ids = TimeZone.getAvailableIDs();
        //for (String id : ids) {
        //  System.out.println(id + ", ");
        //}

        TimeZone tz;
        // 默认时区
        tz = TimeZone.getDefault();
        System.out.println("======default======");
        printDate(tz);

        // GMT +09
        System.out.println("======GMT +09======");
        tz = TimeZone.getTimeZone("GMT+09:00");
        printDate(tz);

        // GMT +04
        System.out.println("======GMT +04======");
        tz = TimeZone.getTimeZone("GMT+04:00");
        printDate(tz);

        // Hongkong
        System.out.println("======Hongkong======");
        tz = TimeZone.getTimeZone("Hongkong");
        printDate(tz);

        // Japan
        System.out.println("======Japan======");
        tz = TimeZone.getTimeZone("Japan");
        printDate(tz);
    }

    private static void printDate(TimeZone tz) {
        Date date = new Date();
        DateFormat df = DateFormat.getInstance();
        df.setTimeZone(tz);
        String str = df.format(date);
        System.out.println(str + "  ---  " + tz.getID());
    }
}

去服务器上测试：