How To Get Last One Month Data in MySQL

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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!