Menu
I'm trying to get a report from my history table by hourly usage. history table is;
CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL DEFAULT '0', `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`history_id`), KEY `user_id` (`user_id`), KEY `created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; I want to group by HOUR and COUNT all records in a given date range.
Hours | Usage ------------------------------------ 00:00-01:00 | 5 01:00-02:00 | 9 02:00-03:00 | 0 (or NULL) 03:00-04:00 | 20 ... ... ... 22:00-23:00 | 11 23:00-00:00 | 1 I used a query something like this but it doesn't display all hours.
SELECT CASE WHEN HOUR(created) BETWEEN 0 AND 1 THEN '00:00 - 01:00' WHEN HOUR(created) BETWEEN 1 AND 2 THEN '01:00 - 02:00' WHEN HOUR(created) BETWEEN 2 AND 3 THEN '02:00 - 03:00' WHEN HOUR(created) BETWEEN 3 AND 4 THEN '03:00 - 04:00' WHEN HOUR(created) BETWEEN 4 AND 5 THEN '04:00 - 05:00' WHEN HOUR(created) BETWEEN 5 AND 6 THEN '05:00 - 06:00' WHEN HOUR(created) BETWEEN 6 AND 7 THEN '06:00 - 07:00' WHEN HOUR(created) BETWEEN 7 AND 8 THEN '07:00 - 08:00' WHEN HOUR(created) BETWEEN 8 AND 9 THEN '08:00 - 09:00' WHEN HOUR(created) BETWEEN 9 AND 10 THEN '09:00 - 10:00' WHEN HOUR(created) BETWEEN 10 AND 11 THEN '10:00 - 11:00' WHEN HOUR(created) BETWEEN 11 AND 12 THEN '11:00 - 12:00' WHEN HOUR(created) BETWEEN 12 AND 13 THEN '12:00 - 13:00' WHEN HOUR(created) BETWEEN 13 AND 14 THEN '13:00 - 14:00' WHEN HOUR(created) BETWEEN 14 AND 15 THEN '14:00 - 15:00' WHEN HOUR(created) BETWEEN 15 AND 16 THEN '15:00 - 16:00' WHEN HOUR(created) BETWEEN 16 AND 17 THEN '16:00 - 17:00' WHEN HOUR(created) BETWEEN 17 AND 18 THEN '17:00 - 18:00' WHEN HOUR(created) BETWEEN 18 AND 19 THEN '18:00 - 19:00' WHEN HOUR(created) BETWEEN 19 AND 20 THEN '19:00 - 20:00' WHEN HOUR(created) BETWEEN 20 AND 21 THEN '20:00 - 21:00' WHEN HOUR(created) BETWEEN 21 AND 22 THEN '21:00 - 23:00' WHEN HOUR(created) BETWEEN 22 AND 23 THEN '22:00 - 23:00' WHEN HOUR(created) BETWEEN 23 AND 24 THEN '23:00 - 00:00' END AS `Hours`, COUNT(*) AS `usage` FROM history WHERE (created BETWEEN '2012-02-07' AND NOW()) GROUP BY CASE WHEN HOUR(created) BETWEEN 0 AND 1 THEN 1 WHEN HOUR(created) BETWEEN 1 AND 2 THEN 2 WHEN HOUR(created) BETWEEN 2 AND 3 THEN 3 WHEN HOUR(created) BETWEEN 3 AND 4 THEN 4 WHEN HOUR(created) BETWEEN 4 AND 5 THEN 5 WHEN HOUR(created) BETWEEN 5 AND 6 THEN 6 WHEN HOUR(created) BETWEEN 6 AND 7 THEN 7 WHEN HOUR(created) BETWEEN 7 AND 8 THEN 8 WHEN HOUR(created) BETWEEN 8 AND 9 THEN 9 WHEN HOUR(created) BETWEEN 9 AND 10 THEN 10 WHEN HOUR(created) BETWEEN 10 AND 11 THEN 11 WHEN HOUR(created) BETWEEN 11 AND 12 THEN 12 WHEN HOUR(created) BETWEEN 12 AND 13 THEN 13 WHEN HOUR(created) BETWEEN 13 AND 14 THEN 14 WHEN HOUR(created) BETWEEN 14 AND 15 THEN 15 WHEN HOUR(created) BETWEEN 15 AND 16 THEN 16 WHEN HOUR(created) BETWEEN 16 AND 17 THEN 17 WHEN HOUR(created) BETWEEN 17 AND 18 THEN 18 WHEN HOUR(created) BETWEEN 18 AND 19 THEN 19 WHEN HOUR(created) BETWEEN 19 AND 20 THEN 20 WHEN HOUR(created) BETWEEN 20 AND 21 THEN 21 WHEN HOUR(created) BETWEEN 21 AND 22 THEN 22 WHEN HOUR(created) BETWEEN 22 AND 23 THEN 23 WHEN HOUR(created) BETWEEN 23 AND 24 THEN 24 END It displays only if there are any records.
Hours | Usage ------------------------------------ 00:00-01:00 | 5 01:00-02:00 | 9 23:00-00:00 | 1 
560
SELECT HOUR(dt), WEEKDAY(dt), COUNT(*) FROM tbl GROUP BY HOUR(dt), WEEKDAY(dt) ORDER BY HOUR(dt), WEEKDAY(dt); Second, you need to "pivot" the table. However, you have particular columns. See http://mysql.rjweb.org/doc.php/pivot for how to generate the code for pivoting.
Here is the SQL query to get data for every hour in MySQL. In the above query, we simply group by order_date using HOUR function and aggregate amount column using SUM function. HOUR function retrieves hour number from a given date/time/datetime value, which can be provided as a literal string or column name.
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.
Your existing query can be reduced to:
SELECT CONCAT(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours , COUNT(*) AS `usage` FROM history WHERE created BETWEEN '2012-02-07' AND NOW() GROUP BY HOUR(created) To display every hour, including those for which there is no data, you need to outer join with a table containing all the hours for which you want data. You can build such a table in your query using UNION:
SELECT CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours , COUNT(created) AS `usage` FROM history RIGHT JOIN ( SELECT 0 AS Hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 ) AS AllHours ON HOUR(created) = Hour WHERE created BETWEEN '2012-02-07' AND NOW() OR created IS NULL GROUP BY Hour ORDER BY Hour However, the treatment of groups for which no data exists is really a matter for business logic that's best placed in your data access layer rather than in the database itself: indeed it should be trivial for your application to use a zero value whenever an hour is absent.
143
Modifying on @eggyal answer, as another good use case would be to display day as well along with hours.
Suppose you need COUNT of records of past 7 days which is of 24 hour each.
SELECT dayname(date_sub(curdate(), INTERVAL 1 DAY)) AS Day, CONCAT(Hour, ':00-', Hour+1, ':00') AS Hour, COUNT(created) AS `usage` FROM history RIGHT JOIN ( SELECT 0 AS Hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 ) AS totalhours ON HOUR(created) = HOUR AND DATE(created) = date_sub(curdate(), INTERVAL 1 DAY) OR created IS NULL GROUP BY Hour ORDER BY Hour; 
Modifying this in the business logic of the backend code or in a stored procedure,
INTERVAL 1 DAY can yield query results of past 7 days along with the name of the day.
43
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
