How to Get Data For Every Hour in MySQL

Last updated on July 23rd, 2024 at 07:32 am

MySQL is a popular database system used by many organizations and teams all around the world. It provides excellent data analysis and reporting capabilities. Often database programmers and data analysts need to retrieve data for every hour in MySQL database. This is especially required if you have a high traffic website that generates a lot of data every hour. In such cases, your team will need to analyze data every hour and you will 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

We will basically retrieve the hour value from datetime or time column and then use it in GROUP BY clause to group data by hour. You can use the same approach for getting data by other units of time such as day, minute, etc. MySQL provides DATE(), MINUTE() functions to get date and minute respectively.

We will need to use HOUR() function to get the hour value from a datetime column. HOUR function retrieves hour number from a given date/time/datetime value, which can be provided as a literal string or column name. Here is its syntax.

SELECT HOUR(column_name) AS hour FROM table_name;

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 |
 +------+---------------------+--------+

We will look at several important use cases to aggregate hourly data in MySQL.

Group data By Hour (0-23)

This is the simplest case where we just want to group our table’s data for every hour. Here is the SQL query to get data for every hour in MySQL. We use HOUR() function with GROUP BY clause. Please note, you need to use the same HOUR() function and column name in both SELECT as well as GROUP BY parts of your SQL query. This query will group data for each hour of day (0-23).

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.

Filter Data Before Grouping

You can filter the input data used for aggregation by using WHERE clause as shown below. Here is an example to use data between dates Feb 1, 2021 and March 1, 2021.

mysql> select hour(order_date),sum(amount)
       from sales
       where order_date between '2021-02-01' and '2021-03-01'
       group by hour(order_date);

Group Data by Date & Hour

In the above example, all data is grouped within hours 0-23 irrespective of the dates. In order to avoid mixing data pertaining to different dates, you may need to aggregate it by date and hour. You can do this by using date() and hour() functions as shown below.

ELECT DATE(column_name) AS date, HOUR(column_name) AS hour, COUNT(*)
FROM table_name
GROUP BY DATE(column_name), HOUR(column_name);

Here is an example to aggregate sales data by date and hour.

mysql> select date(order_date),hour(order_date),sum(amount)
       from sales
       group by date(order_date),hour(order_date);

Please note, in this case, you need to call date() and hour() functions in both SELECT as well as GROUP BY clauses.

Group Data By AM/PM

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. Here is the syntax of date_format() function.

DATE_FORMAT(date, format)

You need to provide the date value as literal string or column name. The format specifiers can be one of these.

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 |
 +-------+-------------+

Please note, in this query also, you need to mention the same date_format() function in both SELECT as well as GROUP clauses of your SQL query. date_format() function is very useful to round timestamp values.

Group Data in Different Timezones

If your data contains datetime values for different time zones, then you will need to convert them all into the same time zone such as UTC (Coordinated Time) before grouping it. You can easily convert datetime to UTC in MySQL using convert_tz() function. Here is an example to change time zone of column before grouping it by the hour.

mysql> select hour(CONVERT_TZ(order_date, '+00:00', your_timezone)),sum(amount)
       from sales
       group by hour(CONVERT_TZ(order_date, '+00:00', your_timezone));

Conclusion

Many organizations needs to report MySQL data by date and hour. In this article, we have learnt several simple ways to quickly aggregate data by hour, date and hour, as AM/PM. There are broadly two ways to aggregate data by time intervals – using built time functions such as HOUR(), DATE(), etc. and using date_format() function. You can use either of these methods. We recommend using date_format() function because it is super versatile and supports a vast range of time series aggregations. Please remember, if your data is involves different time zones, then it is advisable to convert it into a consistent time zone before you aggregate it.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Get Last 1 hour data in MySQL
How to fetch rows not present in another table
How to Get Current Week Data in MySQL