通常我们在安装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');