DEV Community

Techsolutionstuff
Techsolutionstuff

Posted on • Originally published at techsolutionstuff.com

How To Get Hourly Data In MySQL Query

In this article, we will see how to get hourly data in MySQL Query. 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.

Get Hour Wise Data Using MySQL Example:

Using SQL query to get data for every hour in MySQL.

id order_date amount
1 2022-09-29 08:15:00 150
2 2022-09-29 08:30:00 200
3 2022-09-29 08:55:00 300
4 2022-09-29 09:20:00 400
5 2022-09-29 10:45:00 450
7 2022-09-29 11:50:00 200

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);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Latest comments (0)