Sometimes you may need to get hourly data, or fetch records for every hour in MySQL. In this article, we will look at how to get data for every hour in MySQL.
How to Get Data For Every Hour in MySQL
Here are the steps to get data for every hour in MySQL. Let us say you have the following table sales(id, order_date, amount)
mysql> create table sales(id int,
order_date datetime,
amount int);
mysql> insert into sales(id, order_date, amount)
values(1, '2021-02-02 08:15:00',250),
(2, '2021-02-02 08:30:00',200),
(3, '2021-02-02 08:55:00',150),
(4, '2021-02-02 09:15:00',125),
(5, '2021-02-02 09:30:00',250),
(6, '2021-02-02 09:45:00',200),
(7, '2021-02-02 10:15:00',180),
(8, '2021-02-02 10:30:00',125),
(9, '2021-02-02 10:45:00',200),
(10, '2021-02-02 11:15:00',250),
(11, '2021-02-02 11:30:00',150),
(12, '2021-02-02 11:45:00',200);
mysql> select * from sales;
+------+---------------------+--------+
| id | order_date | amount |
+------+---------------------+--------+
| 1 | 2021-02-02 08:15:00 | 250 |
| 2 | 2021-02-02 08:30:00 | 200 |
| 3 | 2021-02-02 08:55:00 | 150 |
| 4 | 2021-02-02 09:15:00 | 125 |
| 5 | 2021-02-02 09:30:00 | 250 |
| 6 | 2021-02-02 09:45:00 | 200 |
| 7 | 2021-02-02 10:15:00 | 180 |
| 8 | 2021-02-02 10:30:00 | 125 |
| 9 | 2021-02-02 10:45:00 | 200 |
| 10 | 2021-02-02 11:15:00 | 250 |
| 11 | 2021-02-02 11:30:00 | 150 |
| 12 | 2021-02-02 11:45:00 | 200 |
+------+---------------------+--------+
Also read : How to Get Last 1 hour data in MySQL
How to get data for every hour in MySQL
Here is the SQL query to get data for every hour in MySQL.
mysql> select hour(order_date),sum(amount)
from sales
group by hour(order_date);
+------------------+-------------+
| hour(order_date) | sum(amount) |
+------------------+-------------+
| 8 | 600 |
| 9 | 575 |
| 10 | 505 |
| 11 | 600 |
+------------------+-------------+
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.
Also read : How to fetch rows not present in another table
If you want to display hour values using AM/PM notation such as 8AM, 9AM , etc, then you need to use date_format function that allows you to change the format of a given date/time/datetime value, which can be provided as a literal string or column name.
mysql> select date_format(order_date,'%H %p') as hour,
sum(amount) as total_sales
from sales
group by date_format(order_date,'%H %p');
+-------+-------------+
| hour | total_sales |
+-------+-------------+
| 08 AM | 600 |
| 09 AM | 575 |
| 10 AM | 505 |
| 11 AM | 600 |
+-------+-------------+
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
