Sometimes you may need to get rows from last 30 days or get last 1 month data in MySQL. It is easy to get records from last 30 days in MySQL even though there is no built-in function for it. Here’s the SQL query to select records for last 30 days.
How to Get Records from Last 30 Days in MySQL
Here’s the SQL to get records from last 30 days. Let’s say you have the following table sales(order_date, sale) that contains daily sales data.
mysql> create table sales(order_date date,sale int);
mysql> insert into sales(order_date, sale)
values('2020-05-04',230),
('2020-05-05',200),
('2020-05-06',210),
('2020-05-07',180),
('2020-05-08',220),
('2020-05-09',230),
('2020-05-10',220),
('2020-05-11',225),
('2020-05-12',200),
('2020-05-13',210),
('2020-05-14',190),
('2020-05-15',200),
('2020-05-16',220),
('2020-05-17',210),
('2020-05-18',190),
('2020-05-19',180),
('2020-05-20',250),
('2020-05-21',240),
('2020-05-22',245),
('2020-05-23',230),
('2020-05-24',220),
('2020-05-25',210),
('2020-05-26',130),
('2020-05-27',200),
('2020-05-28',210),
('2020-05-29',221),
('2020-05-30',235),
('2020-05-31',233),
('2020-06-01',237),
('2020-06-02',230),
('2020-06-03',220),
('2020-06-04',210),
('2020-06-05',200),
('2020-06-06',260),
('2020-06-07',270),
('2020-06-08',240),
('2020-06-09',290),
('2020-06-10',230);
mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-04 | 230 |
| 2020-05-05 | 200 |
| 2020-05-06 | 210 |
| 2020-05-07 | 180 |
| 2020-05-08 | 220 |
| ... | ... |
| ... | ... |
| 2020-06-06 | 260 |
| 2020-06-07 | 270 |
| 2020-06-08 | 240 |
| 2020-06-09 | 290 |
| 2020-06-10 | 230 |
+------------+------+
Bonus Read : How to Get Records Between 2 Dates in MySQL
How to get records from last 30 days
Here’s the SQL query to get records from last 30 days in MySQL.
mysql> select * from sales
where order_date > now() - INTERVAL 30 day;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-12 | 200 |
| 2020-05-13 | 210 |
| 2020-05-14 | 190 |
| ... | ... |
| ... | ... |
| 2020-06-08 | 240 |
| 2020-06-09 | 290 |
| 2020-06-10 | 230 |
+------------+------+
In the above query we select those records where order_date falls after a past interval of 30 days. We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 30 days in the past.
You can also use current_date instead of now()
mysql> select * from sales
where order_date > current_date - interval 30 day;
Bonus Read : How to Get Records from Last 24 Hours in MySQL
How to Get Last 1 Month data
Here’s the SQL query to get last 1 month records in MySQL.
mysql> select * from sales
where order_date > current_date - interval 1 month;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-05-11 | 225 |
| 2020-05-12 | 200 |
| 2020-05-13 | 210 |
| ... | ... |
| ... | ... |
| 2020-06-08 | 240 |
| 2020-06-09 | 290 |
| 2020-06-10 | 230 |
+------------+------+
Bonus Read : How to Get Records of Current Month in MySQL
In the above query, we select rows where order_date falls after past 1 month interval. We use ‘1 month’ argument for INTERVAL clause, instead of using ’30 day’
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.