How To Get Last One Month Data in MySQL

Last updated on July 16th, 2024 at 05:47 am

Sometimes you may need to fetch last month record or get rows from previous month. Here’s how to get last one month data in MySQL.

Table of Contents

How To Get Last One Month Data in MySQL

Here are the steps to get last one month data in MySQL.

Let’s say you have the following table orders(id, order_date, amount) in MySQL that contains daily order amounts.

mysql> create table orders(id int, order_date date, amount int);

mysql> insert into orders(id,order_date, amount)
     values(1,'2020-07-25',250),
     (2,'2020-07-26',350),
     (3,'2020-07-27',200),
     (4,'2020-07-28',150),
     (5,'2020-07-29',350),
     (6,'2020-07-30',300),
     (7,'2020-07-31',250),
     (8,'2020-08-01',550),
     (9,'2020-08-02',350),
     (10,'2020-08-03',250),
     (11,'2020-08-04',200);

mysql> select * from orders;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-25 |    250 |
|    2 | 2020-07-26 |    350 |
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
|    5 | 2020-07-29 |    350 |
|    6 | 2020-07-30 |    300 |
|    7 | 2020-07-31 |    250 |
|    8 | 2020-08-01 |    550 |
|    9 | 2020-08-02 |    350 |
|   10 | 2020-08-03 |    250 |
|   11 | 2020-08-04 |    200 |
+------+------------+--------+

Bonus Read : How to Copy Table in MySQL

How to get last one month record in MySQL

Here’s the SQL query to get last one month record in MySQL

mysql> select * from orders
     where month(order_date)=month(now())-1;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-07-25 |    250 |
|    2 | 2020-07-26 |    350 |
|    3 | 2020-07-27 |    200 |
|    4 | 2020-07-28 |    150 |
|    5 | 2020-07-29 |    350 |
|    6 | 2020-07-30 |    300 |
|    7 | 2020-07-31 |    250 |
+------+------------+--------+

In the above query, we select only those records whose month is one less than the month of current date. We obtain month number of dates using MONTH() function. It is very useful to group data by month in MySQL. We obtain current date using NOW() function.

As per our data, since the current month is August, we get the records for previous month, that is, July.

Hopefully, now you can easily get last one month data in MySQL.

Bonus Read : How to Add NOT NULL constraint in MySQL

Similarly, if you want to get records for past one month rolling, that is, last 30 days, then here’s the SQL query for it.

select * from orders
where order_date>now() - interval 1 month;

In the above query, we select rows after past 1 month interval. We use INTERVAL clause and NOW() function to obtain the date 1 month in the past, from present date.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Add Total Row in MySQL

How to Calculate Retention Rate in SQL?

MySQL Change Collation of All Tables