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

posted @ 2020-10-25 13:39  菩提树下的杨过  阅读(192)  评论(0编辑  收藏