MySQL: Records inserted by hour, for the last 24 hours

Question

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.

Answer 1

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.