mysql 时区设定_教你如何修改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());

}

}

去服务器上测试：

完成！如果觉得我的文章能够帮助到你，请多多关注、多多分享，各位的支持就是我前进的动力。

慕课网，程序员的梦工厂！

我可以对一个人无限的好，前提是值得。 ——慕冬雪