How To Get Last 1 Hour Data in MySQL

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

Database programmers and data analysts often need to fetch data for last 1 hour for reporting and analysis. MySQL provides tons of useful functions and statements for time series reporting. It is good to learn how to get last 1 hour records, or retrieve last 1 hour rows for reporting and analysis. There are several ways to do this. In this article, we will look at how to get last 1 hour data in MySQL.

Table of Contents

How To Get Last 1 Hour Data in MySQL

It is very easy to select last 1 hour data in MySQL using INTERVAL clause. 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',150),
      (3, '2021-02-02 08:55:00',200),
      (4, '2021-02-02 09:15:00',125),
      (5, '2021-02-02 09:30:00',200),
      (6, '2021-02-02 09:45:00',250);

 mysql> select * from sales;
 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    1 | 2021-02-02 08:15:00 |    250 |
 |    2 | 2021-02-02 08:30:00 |    150 |
 |    3 | 2021-02-02 08:55:00 |    200 |
 |    4 | 2021-02-02 09:15:00 |    125 |
 |    5 | 2021-02-02 09:30:00 |    200 |
 |    6 | 2021-02-02 09:45:00 |    250 |
 +------+---------------------+--------+

We will learn 3 different ways to get data for last 1 hour in MySQL.

1. Using Now() function

Now() is a built-in MySQL function that returns the present datetime value, when invoked. You can use this to get data for past 1 hour. Here is the SQL to show latest time using now() function.

mysql> select now();
 +---------------------+
 | now()               |
 +---------------------+
 | 2021-02-02 09:48:27 |
 +---------------------+

Here is the SQL to get last 1 hour data in MySQL.

mysql> select * 
       from sales 
       where order_date > now() - interval 1 hour;
 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    3 | 2021-02-02 08:55:00 |    200 |
 |    4 | 2021-02-02 09:15:00 |    125 |
 |    5 | 2021-02-02 09:30:00 |    200 |
 |    6 | 2021-02-02 09:45:00 |    250 |
 +------+---------------------+--------+

In the above query, we select only those rows whose order_date falls within past 1 hour interval. We use INTERVAL clause to easily substract 1 hour interval from present time obtained using now() function. INTERVAL clause allows you to add or subtract a certain interval of time from a date time value in MySQL. This value can be a literal string or a datetime column.

2. Using Date_sub() function

Alternatively, you can also use date_sub() function to retrieve data for past 1 hour in MySQL. It basically subtracts a user specific interval of time from a date literal string or column and returns the result. In the following query, we select rows where order_date value is greater than or equal to the value returned by date_sub() function after subtracting 1 hour from now() function’s result, that is, the current datetime.

mysql> select * 
       from  sales
       where order_date >= DATE_SUB(NOW(),INTERVAL 1 HOUR); 
 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    3 | 2021-02-02 08:55:00 |    200 |
 |    4 | 2021-02-02 09:15:00 |    125 |
 |    5 | 2021-02-02 09:30:00 |    200 |
 |    6 | 2021-02-02 09:45:00 |    250 |
 +------+---------------------+--------+

3. How to Get Current Hour Data in MySQL

Sometimes you may need to get records of current hour in MySQL. This is slightly different from getting past 1 hour’s data but can be seen as its variation. Here we need the data pertaining to the present hour only. Here is the SQL query to retrieve current hour data.

mysql> select * from sales 
       where date(order_date)=date(now()) 
        and hour(order_date)=hour(now());
 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    4 | 2021-02-02 09:15:00 |    250 |
 |    5 | 2021-02-02 09:30:00 |    250 |
 |    6 | 2021-02-02 09:45:00 |    250 |
 +------+---------------------+--------+

In the above SQL query, we select only those records whose order_date column’s DATE and HOUR values are same as those of present datetime obtained using now() function. We use DATE and HOUR functions to get date and hour values from datetime values. You can also use date_format() function to round datetime values to nearest hour.

Conclusion

In this article, we have learnt several simple ways to get data pertaining to past 1 hour in MySQL. The key is to select rows where your datetime column’s value is greater than or equal to present time minus 1 hour. You can do this subtraction using plain mathematical operator ‘-‘ or using date_sub() function. In both cases, you need to use INTERVAL clause to get a time interval of 1 hour. Being able to get data for past 1 hour is very useful especially in reporting environments where your team needs to constantly keep track of latest happenings on your website.

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 show rows not present in another table in MySQL
How to Get Current Week Data in MySQL
Select Top 10 Records for Each Category in MySQL
Common Table Expression in MySQL