背景
数据库存的是东八区时间,需要根据用户时区时间按 天,周,月统计数据
分析
将用户时区时间转化为东八区查数据库,查到结果再转化为用户时区
但这里涉及到用户时间分组,若分组的维度是东八区,那根据东八区聚合会导致数据计算错误。需要按用户时区分组
想过一种方案是将 数据库关于统计相关表加上时区字段,每条记录根据24个时区生成24条记录。这样会产生大量冗余数据(原来1万条,现在24万),影响数据库性能
后来找到更优方案
使用mysql函数,将数据库中原来东八区数据计算为用户时区时间,根据计算后的时间查询
eg:
select DATE_FORMAT(CONVERT_TZ(a.create_time,"+8:00",'-12:00'),'%Y-%m-%d %H') AS create_time,
SUM(a.request_num) AS request_num,
SUM(a.delivered_num) AS delivered_num,
SUM(a.click_num) AS click_num,
SUM(a.open_num) AS open_num
from `stat_every_hour` a
WHERE a.create_time>='2020-04-09 20' and a.send_hour_timezone<='2020-04-10 19'
GROUP BY create_time
CONVERT_TZ(a.create_time,"+8:00",#formatTimeZone#) 函数 第一个参数为转化字段,第二个参数为当前时区,第三个参数为目标时区。注意格式