原创

记一次修改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

预想结果应该为:

记一次修改mysql时区的历程文章的图片

但实际SQL结果为:

记一次修改mysql时区的历程文章的图片

多出来整整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

记一次修改mysql时区的历程文章的图片

 OK,已恢复正常!

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

vi /etc/mysql/my.cnf

记一次修改mysql时区的历程文章的图片

然后在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());
	}
}

去服务器上测试:

记一次修改mysql时区的历程文章的图片


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

正文到此结束