In this tutorial, we will see how to get hourly data in mysql. Many times we need to get hourly data or we are required to get data every hour in MySQL. For getting data for every hour in MySQL we use the hour() function. MySQL hour() returns the hour of a time. The return value is within the range of 0 to 23 for time-of-day values.
So, let's see SQL query to get data for every hour or MySQL query to get hour wise records in PHP and how to get hourly data in PHP from the database.
Using SQL query to get data for every hour in MySQL.
id | order_date | amount
---+---------------------+-------
1 | 2022-02-02 08:15:00 | 150
2 | 2022-02-02 08:30:00 | 200
3 | 2022-02-02 08:55:00 | 100
4 | 2022-02-02 09:20:00 | 300
5 | 2022-02-02 10:45:00 | 200
7 | 2022-02-02 11:50:00 | 190
In your table, the record needs to be like date and hour.
select hour(order_date), sum(amount)
from users
group by hour(order_date);
If you want to display hour values using AM/PM notation such as 10 AM, 11 AM, 12 PM, etc, then you need to use the date_format function that allows you to change the format of a given date and time.
select date_format(order_date,'%H %p') as hour,
sum(amount) as total_amount
from users
group by date_format(order_date,'%H %p');
You might also like :
In this example we will see how to search object by id and remove it from json array in javascript. we need to add ...
May-26-2021
As an Angular 15 developer, I understand the importance of incorporating image upload functionality with a progress bar...
Jun-21-2023
As a web developer, I understand the significance of embracing the latest technologies to stay ahead in the dynamic worl...
Aug-04-2023
In this tutorial we will see how to import CSV file in MySQL using Node.js. Import and export CSV/EXCEL file in Nod...
Jul-30-2021