MySQL 查询组按日期(12 小时间隔)

发布于04月14日

我编写了一个sql查询，用于获取在一段时间内创建的用于绘制图形(grafana或chart js)的用户数，我的sql查询是

 SELECT
  date(user.created_date) as "time",
  count(distinct user.id) as Number Of User,
  status as status
FROM user
WHERE
  created_date >= FROM_UNIXTIME(1649635200) AND 
  created_date < FROM_UNIXTIME(1649894399)
GROUP BY user.status, date(user.created_date)
ORDER BY date(user.created_date)

Here in this query created date is passed dynamically from front-end, Now i am getting the result like,

现在，每当我从过go 24小时/12小时中 Select 日期过滤器时，一些结果都不存在，

有没有办法将我的sql查询修改为按创建日期分组，间隔12小时

推荐答案

在格拉法纳有一个$__timeFrom()和$__timeTo()的档案

SELECT
  (CASE 
    WHEN HOUR(TIMEDIFF($__timeFrom(), $__timeTo())) <= 24
    THEN user.created_date
    ELSE date(user.created_date) end) AS "time",
  count(distinct user.id) as Users,
FROM user
WHERE
  user.created_date >= $__timeFrom() AND 
  user.created_date < $__timeTo() AND
GROUP BY CASE
  when HOUR(TIMEDIFF($__timeFrom(), $__timeTo())) <= 24
  then user.created_date
  else date(created_date) end
ORDER BY CASE
  when HOUR(TIMEDIFF($__timeFrom(), $__timeTo())) <= 24
  then user.created_date
  else date(created_date) end;