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.
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. 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!
