MySQL 查询按日期分组（12 小时间隔）

Question

我编写了一个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)

在这个查询中创建日期是从前端动态传递的， 现在我得到的结果是这样的

现在，每当我选择过去 24 小时/12 小时的日期过滤器时，有些结果就不存在，

有没有办法修改我的 sql 查询，以按 12 小时间隔的created_date 进行分组 例如，现在查询结果是 11/04/2022 - 5 个用户（已创建应用程序） 我想要这样的查询结果 11/04/2022 00:00:00 2 - 2 个用户创建于 11/04/2022 12:00:00 - 已创建 3 个用户

Answer 1

在grafana中有一个归档的$__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;

Answer 2

如果您通过在组中使用此扩展，则将获得12小时的分组。

DATE(created_date) + INTERVAL (HOUR(created_date) - HOUR(created_date) MOD 12) HOUR

如果您拥有PRIV，则可以声明存储的功能，以使其更易于阅读。

DELIMITER $
DROP FUNCTION IF EXISTS TRUNC_HALFDAY$
CREATE
  FUNCTION TRUNC_HALFDAY(datestamp DATETIME)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'truncate to 12 hour boundary. Returns the nearest
           preceding half-day (noon, or midnight)'
  RETURN DATE(datestamp) +
                INTERVAL (HOUR(datestamp) -
                          HOUR(datestamp) MOD 12) HOUR$
DELIMITER ;

您也可以执行此操作

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

然后，即使该功能在查询中出现3次，

，因为它已声明为确定性它只会调用一次每行一次。更完整的写入在这里。