How to get the records of only 30 days in MySQL?

 

In MySQL, you might occasionally need to retrieve rows from the previous 30 or 12 days. Despite their not being a built-in method in MySQL, it is simple to get entries from the last 30 days. This SQL query will pick records from the 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),
     ('2022-05-05',200),
     ('2022-05-06',210),
     ('2022-05-07',180),
     ('2022-05-08',220),
     ('2022-05-09',230),
     ('2022-05-10',220),
     ('2022-05-11',225),
     ('2022-05-12',200),
     ('2022-05-13',210),
     ('2022-05-14',190),
     ('2022-05-15',200),
     ('2022-05-16',220),
     ('2022-05-17',210),
     ('2022-05-18',190),
     ('2022-05-19',180),
     ('2022-05-20',250),
     ('2022-05-21',240),
     ('2022-05-22',245),
     ('2022-05-23',230),
     ('2022-05-24',220),
     ('2022-05-25',210),
     ('2022-05-26',130),
     ('2022-05-27',200),
     ('2022-05-28',210),
     ('2022-05-29',221),
     ('2022-05-30',235),
     ('2022-05-31',233),
     ('2022-06-01',237),
     ('2022-06-02',230),
     ('2022-06-03',220),
     ('2022-06-04',210),
     ('2022-06-05',200),
     ('2022-06-06',260),
     ('2022-06-07',270),
     ('2022-06-08',240),
     ('2022-06-09',290),
     ('2022-06-10',230);

mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2022-05-04 |  230 |
| 2022-05-05 |  200 |
| 2022-05-06 |  210 |
| 2022-05-07 |  180 |
| 2022-05-08 |  220 |
|            |      |
|        ... |  ... |
| 2022-06-06 |  260 |
| 2022-06-07 |  270 |
| 2022-06-08 |  240 |
| 2022-06-09 |  290 |
| 2022-06-10 |  230 |
+------------+------+

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 |
+------------+------+
| 2022-05-12 |  200 |
| 2022-05-13 |  210 |
| 2022-05-14 |  190 |
|        ... |  ... | 
|        ... |  ... | 
| 2022-06-08 |  240 |
| 2022-06-09 |  290 |
| 2022-06-10 |  230 |
+------------+------+

In the aforementioned query, we only include entries whose order date occurs more than 30 days ago. We obtain the most recent datetime value using the system function now(), and we compute a date 30 days in the past using the INTERVAL clause.

You can also use current_date instead of now()

mysql> select * from sales
     where order_date > current_date - interval 30 day;