Menu
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.
562
In the above SQL query, we use MySQL system function now() to get current datetime. Then we use INTERVAL clause to select those rows where order_date falls within past 24 hours of present datetime. Instead of specifying interval in hours, you can also mention it in day.
Here is the SQL to show latest time using now() function. Here is the SQL to get last 1 hour data in MySQL. In the above query, we select only those rows whose order_date falls within past 1 hour interval. We use INTERVAL clause to easily substract 1 hour interval from present time obtained using now() function.
The date and time data types for representing temporal values are DATE , TIME , DATETIME , TIMESTAMP , and YEAR . Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME .) By default, the current time zone for each connection is the server's time.
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.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
