How To Get Hourly Data In MySQL

Websolutionstuff | Feb-04-2022 | Categories : MySQL

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.

MySQL Example :

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 :

Recommended Post
Featured Post
How To Remove Column From Table In Laravel 10 Migration
How To Remove Column From Tabl...

In this article, we will see how to remove columns from a table in laravel 10 migration. Here, we will learn about...

Read More

Apr-26-2023

Laravel 9 Barcode Generator Example
Laravel 9 Barcode Generator Ex...

In this article, we will see laravel 9 barcode generator example. In this example we will use the milon/barcod...

Read More

Mar-26-2022

How to Search Object by ID and Remove It from JSON Array In JavaScript
How to Search Object by ID and...

In this example we will see how to search object by id and remove it from json array in javascript. we need to add ...

Read More

May-26-2021

How To Convert Image Into Base64 String Using jQuery
How To Convert Image Into Base...

In this example, we will see how to convert an image into a base64 string using jquery. Base64 encoding schemes are...

Read More

Jan-03-2022