从当前时间戳生成MySQL每小时​​细分 [英] Generating a MySQL hourly breakdown from current timestamp

问题描述

恐怕这可能是一个非常尴尬的简单问题-但此刻我的想法完全陷入僵局.

I'm afraid this is probably a very embarrassingly easy question - but my mind is just completely stuck at this hour.

我有一个表格，其中存储了不同人进行的活动的数量及其发生的时间.

I have a table that stores the number of activities carried out by different people, and the time it took place in.

我想创建一个接受该人姓名作为参数的报告，并显示从当前时间戳(now())开始的该人在过去24小时内每小时的活动次数.

I want to create a report that accepts the person's name as a parameter, and show the number of activities per hour for that person during each of the previous 24 hours starting from current timestamp (now()).

现在

SELECT hour(TimeStamp), activities FROM tbl1 
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 24 HOUR)  AND Name = ? 
GROUP BY hour(timestamp)

仅在存在任何活动的那几个小时返回给我.但是，我希望在没有活动的情况下进行24小时的完整细分(零).

only returns to me those hours when any activity was present. However, I want a complete 24 hour breakdown with zero for when there was no activity.

即我要

Hour | No. of Activities
1    | 34
4    | 22
9    | 86

但我想要

Hour | No. of Activities
1    | 34
2    | 0
3    | 0
4    | 22
5    | 0
... etc.

我该怎么做?

(示例中的小时顺序无关紧要)

(The order of hours in the example is irrelevant)

推荐答案

您可以创建hourly表并执行LEFT JOIN

create table hourly
(
  /* hour is not a reserved keyword */
  hour smallint(2) not null default 0
);

insert into hourly values (0),(1).... until 24

SELECT 
  hourly.hour, 
  COALESCE(COUNT(activities),0) AS "No of Activities"
FROM hourly
LEFT JOIN tbl1 ON hourly.hour=hour(tbl1.TimeStamp)
WHERE 
  tbl1.timestamp>=DATE_SUB(NOW(), INTERVAL 24 HOUR) AND 
  tbl1.Name=? 
GROUP BY hourly.hour
ORDER BY hourly.hour;

这篇关于从当前时间戳生成MySQL每小时​​细分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！