Grafana和MySQL相差8小时

背景说明

今日在用 Grafana 进行数据大屏看板制作 (数据源为 MySQL)，其中有个面板是统计 “每日的用户反馈表单数量曲线图”，如图

因为要确保数据的准确性，于是进行了验证，抽样调查了其中的一个表单类型 异常反馈 的数量

select count(1) as total from user_form_request where ct >= CURRENT_DATE() and form_type = 13; 
# 执行结果为 total => 42
select count(1) as total from user_form_request where ct >= '2023-04-11 08:00:00' and form_type = 13; 
# 执行结果为 total => 32

从上面的查询结果表现来看，后者和图中统计的结果数据才对得上（均为 32），也就是说 Grafana 统计的聚合数据比实际偏移了8个小时！

问题排查

从广大的网络文章看，主要集中都是说 Grafana 时区或者 MySQL 时区设置有问题，或者两者的时区对不上！

于是，我先检查看下 Grafana 的设置，看到已经默认设置成我们 东八区

排除了 Grafana 时区问题，需要进一步确定 MySQL 是否也是使用 东八区

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

查询结果显示 SYSTEM，也即跟随服务系统的时区设置。

因为 MySQL 时区随了系统了，所以接下来需要确认服务器的时区情况

# 执行下面命令进行查询
timedatectl

# 输出结果如下
# -------------------------------------------------
      Local time: Tue 2023-04-11 23:09:27 CST
  Universal time: Tue 2023-04-11 15:09:27 UTC
        RTC time: Tue 2023-04-11 15:09:27
       Time zone: n/a (CST, +0800)
     NTP enabled: yes
NTP synchronized: yes
 RTC in local TZ: no
      DST active: n/a

可以看出，服务器其实也是使用了 东八区。

😮 也就是基本可以排除如各种社区说的服务器时区设置问题！

脑洞时刻

考虑到方便进一步定位，我这边获取了 Grafana 对应图表查询的模板内容

select 
	$__timeGroupAlias(ufr.ct,$__interval),
	mf.name as '表单名',
	count(ufr.id) AS '提交量'
from user_form_request ufr 
left join manual_forms mf on ufr.form_type = mf.id 
WHERE
  $__timeFilter(ufr.ct)
GROUP BY  1,ufr.form_type 
ORDER BY $__timeGroup(ufr.ct,$__interval,0)

同时获取了该模板最近一次查询的 SQL

select 
UNIX_TIMESTAMP(ufr.ct) DIV 86400 * 86400 AS "time",
mf.name as '表单名',
count(ufr.id) AS '提交量'
from user_form_request ufr 
left join manual_forms mf on ufr.form_type = mf.id 
WHERE
  ufr.ct BETWEEN FROM_UNIXTIME(1681142400) AND FROM_UNIXTIME(1681228799)
GROUP BY  1,ufr.form_type 
ORDER BY UNIX_TIMESTAMP(ufr.ct) DIV 86400 * 86400

从查询条件 ufr.ct BETWEEN FROM_UNIXTIME(1681142400) AND FROM_UNIXTIME(1681228799) 可知：

当前查询的是 2023-04-11 00:00:00 至 2023-04-11 23:59:59 的数据；这个时间范围是符合预期的（因为是我手动设置的）！

下面我跟进上面的 SQL 获取到对应结果内容

from_unixtime 时间	unix_timestamp 时间	表单名	提交量
2023-04-10 08:00:00	1681084800	封禁申诉	1
2023-04-10 08:00:00	1681084800	模拟器申请	1
2023-04-10 08:00:00	1681084800	充值异常	2
2023-04-10 08:00:00	1681084800	注销账号	1
2023-04-10 08:00:00	1681084800	找回账号	1
2023-04-10 08:00:00	1681084800	异常反馈	10
2023-04-10 08:00:00	1681084800	游戏建议	2
2023-04-11 08:00:00	1681171200	违规举报	34
2023-04-11 08:00:00	1681171200	注销账号	3
2023-04-11 08:00:00	1681171200	充值异常	3
2023-04-11 08:00:00	1681171200	找回账号	26
2023-04-11 08:00:00	1681171200	封禁申诉	10
2023-04-11 08:00:00	1681171200	游戏下载	4
2023-04-11 08:00:00	1681171200	模拟器申请	20
2023-04-11 08:00:00	1681171200	游戏建议	8
2023-04-11 08:00:00	1681171200	流水查询	2
2023-04-11 08:00:00	1681171200	异常反馈	33

⁉️ 是不是瞬间来疑问了：为何时间格式化会出现 4月10日 数据

茅塞顿开

回归之前说的 Grafana 和 MySQL 时差偏移 8h 的问题，再结合上面的数据和 SQL，我们是不是有足够的资本来怀疑 UNIX_TIMESTAMP(xxx) 的机制导致了这个问题呢？

让我们来看看 官网关于 unix_timestamp 的说明

If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since ‘1970-01-01 00:00:00’ UTC. The server interprets date as a value in the session time zone and converts it to an internal Unix timestamp value in UTC.

也就是说 UNIX_TIMESTAMP() 会把我们的时间（此处假设是 2023-04-11 07:00:00）转成对应的 UTC 时间，而我们当前的服务器使用的是 东八区，所以转成 UTC 时间的时候就会减去 8h，即变成 UTC 时间 2023-04-10 23:00:00。

然后结合上面 SQL 来代入 UNIX_TIMESTAMP('2023-04-11 07:00:00') DIV 86400 * 86400，本身是要取当天的零点（即 2023-04-11 00:00:00），最后因为被转成 UTC 2023-04-10 23:00:00，最后获得的是 2023-04-10 00:00:00；

而如果此时使用 FROM_UNIXTIME(UNIX_TIMESTAMP('2023-04-11 07:00:00') DIV 86400 * 86400)，因为 MySQL 会根据本身的时区进行优化可阅读性，会格式成 东八区 时间，所以在 2023-04-10 00:00:00 的基础上加上 8h，造就了前面数据表看到的 2023-04-10 08:00:00。

解决方案

既然 UNIX_TIMESTAMP() 会默认帮我们减 8h，那我们只要默认给时间加 8h 再进行处理不就行了吗，下面是改造后的 Grafana 查询模板:

select
  $__timeGroupAlias(tmp.my_time,$__interval),
  tmp.name as '表单名',
  count(tmp.id) AS '提交量'
from (
  select 
  CONVERT_TZ(ufr.ct, '+00:00', '+08:00') as my_time,
  mf.name as name,
  ufr.id
  from user_form_request ufr 
  left join manual_forms mf on ufr.form_type = mf.id 
  WHERE
  $__timeFilter(ufr.ct)
) tmp
group by 1,tmp.name
ORDER BY $__timeGroup(tmp.my_time,$__interval,0)

至此，问题圆满解决，希望本次经验能为其他小伙伴也带来一些帮助！

有其他想法或者需要帮助的小伙伴欢迎在评论区一起交流！