MySQL:在小时内插入的记录,最近24小时 [英] MySQL: Records inserted by hour, for the last 24 hours
问题描述
我试图列出最近24小时内插入数据库的每小时记录数。每行显示插入的记录,以及几小时以前的记录。
这是我的查询:
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt gt ; DATE_SUB(NOW(),INTERVAL 24 HOUR)
GROUP BY HOUR(时间)
ORDER BY时间ASC
现在返回:
28 23
62 23
14 20
1 4
28 3
19 1
从23小时前开始,每小时只能显示一个。
我认为它与使用NOW()而不是在小时开始时间相关联,我不确定如何获取。
如果您按 HOUR(时间)
那么你应该在你的选择表达式中使用 HOUR(time)
,而不是 time
。例如:
SELECT HOUR(time),COUNT(*)
FROM`records`
WHERE时间> DATE_SUB(NOW(),INTERVAL 24 HOUR)
GROUP BY HOUR(时间)
ORDER BY HOUR(时间)
或者,您可以按要返回的表达式进行分组:
SELECT COUNT(*) ,FLOOR(TIME_TO_SEC(TIMEDIFF(NOW(),time))/ 3600)
FROM`records`
WHERE time> DATE_SUB(NOW(),INTERVAL 24 HOUR)
GROUP BY FLOOR(TIME_TO_SEC(TIMEDIFF(NOW(),time))/ 3600)
ORDER BY FLOOR(TIME_TO_SEC(TIMEDIFF(NOW(),time) )/ 3600)
如果您想知道,可以安全地调用 NOW()
多次在同一个查询中这样。从手册:
在查询执行开始时,每个查询返回当前日期或时间的函数只计算一次。这意味着在单个查询中对诸如NOW()之类的函数的多次引用总是产生相同的结果。
I'm trying to list the number of records per hour inserted into a database for the last 24 hours. Each row displays the records inserted that hour, as well as how many hours ago it was.
Here's my query now:
SELECT COUNT(*), FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
FROM `records`
WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(time)
ORDER BY time ASC
right now it returns:
28 23
62 23
14 20
1 4
28 3
19 1
That shows two rows from 23 hours ago, when it should only show one per hour. I think it has something to do with using NOW() instead of getting the time at the start of the hour, which I'm unsure on how to get.
There must be a simpler way of doing this.
If you grouped by HOUR(time)
then you should use HOUR(time)
in your select expressions, and not time
. For example:
SELECT HOUR(time), COUNT(*)
FROM `records`
WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(time)
ORDER BY HOUR(time)
Alternatively you can group by the expression you want to return:
SELECT COUNT(*), FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
FROM `records`
WHERE time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
ORDER BY FLOOR( TIME_TO_SEC( TIMEDIFF( NOW(), time)) / 3600 )
In case you were wondering, it is safe to call NOW()
multiple times in the same query like this. From the manual:
Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query always produce the same result.
这篇关于MySQL:在小时内插入的记录,最近24小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!