mysql: 多时区的聚合统计

通常我们在安装mysql实例时，都是使用默认的时区（中国大陆的服务器，通常就是GMT+8北京时区），随着业务的发展，如果业务实现了全球化，需要支持(多时区)按当地时间来汇总数据时，就会涉及到时区转换问题。

比如，有下面这张订单表(为简化问题，仅保留了id、下单时间2个字段) - 注：mysql实例为GMT+8时区

按北京时间汇总每天的订单记录数，sql语句如下：

SELECT 
	COUNT(0),DATE_FORMAT(order_time,'%Y-%m-%d') 
FROM t_order 
GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d');

如果按GMT+1时区（即：欧洲地区）来统计的话，上面的数据就不对了，欧洲地区比北京时间早7小时，即：北京时间 2020-10-25 00:00:00，对应于欧洲当地时间 2020-10-24 17:00:00，把这几条记录的order_time转换一下，得到如下表格：

id	order_time(GMT+8)	order_time(GMT+1)
1	2020-10-25 01:00:01.000	2020-10-24 18:00:01.000
2	2020-10-25 02:00:01.000	2020-10-24 19:00:01.000
3	2020-10-25 20:00:01.000	2020-10-25 13:00:01.000
4	2020-10-25 23:00:01.000	2020-10-25 16:00:01.000
5	2020-10-26 02:00:01.000	2020-10-25 19:00:01.000
6	2020-10-26 05:00:01.000	2020-10-25 22:00:01.000
7	2020-10-26 11:00:01.000	2020-10-26 04:00:01.000
8	2020-10-26 15:00:01.000	2020-10-26 08:00:01.000
9	2020-10-26 20:00:01.000	2020-10-26 13:00:01.000

 很明显，按欧洲当地时间来看，24号2条记录，25号4条记录，26号3条记录。幸好，mysql提供了一个CONVERT_TZ函数，可以用于时区转换，基本用法如下：

上面的语句，将GMT+8北京时间2020-10-25 00:00:00，转换成GMT+1欧洲时间2020-10-24 17:00:00，有了这个利器，最开始的sql可以改成：

SELECT 
	COUNT(0),DATE_FORMAT(CONVERT_TZ(order_time, '+08:00', '+01:00'),'%Y-%m-%d') 
FROM t_order 
GROUP BY DATE_FORMAT(CONVERT_TZ(order_time, '+08:00', '+01:00'),'%Y-%m-%d');

　

 

参考：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz