How to Get Last 30 Days Data in MySQL

1. Import Data to the Database
2. Use the System Function now() to Get the Data From the Last 30 Days in MySQL
3. Use current_date() to Get the Data From the Last 30 Days in MySQL
4. Use the System Function date_sub() to Get the Data From the Last 30 Days in MySQL
5. Use the between Command to Get the Data From the Last 30 Days in MySQL
6. Conclusion

This article teaches how to get the last 30 days’ data records from the database using SQL queries.

Import Data to the Database

First, import some sample data into the database using SQL query. Before importing a dataset, we have to create a table under a database.

SQL Query:

create table sales(order_date date, sale int);

The above syntax creates a sales table, with two columns as order_date and sale. Types of order_date and sale are date and int, respectively.

Next, let’s import some data to the table through manual entries.

SQL Query:

insert into sales(order_date, sale)
values ('2022-09-04',230), ('2022-09-05',200), ('2022-09-06',210), ('2022-09-07',180), ('2022-09-08',220), ('2022-09-09',230), ('2022-09-10',220), ('2022-09-11',225), ('2022-09-12',200), ('2022-09-13',210), ('2022-09-14',190),('2022-09-15',200), ('2022-09-16',220), ('2022-09-17',210), ('2022-09-18',190), ('2022-09-19',180), ('2022-09-20',250), ('2022-09-21',240), ('2022-09-22',245), ('2022-09-23',230), ('2022-09-24',220), ('2022-09-25',210), ('2022-09-26',130), ('2022-09-27',200), ('2022-09-28',210), ('2022-09-29',221), ('2022-09-30',235), ('2022-10-01',237), ('2022-10-02',230), ('2022-10-03',220), ('2022-10-04',210), ('2022-10-05',200), ('2022-10-06',260), ('2022-10-07',270), ('2022-10-08',240), ('2022-10-07',290), ('2022-10-10',230);

Output:

After importing data, we can now extract the data as per our requirements.

This article will elaborate on three methods to get data from the database from the last 30 days. Even though there are no built-in functions to obtain records from the last 30 days in MySQL, using the below SQL queries will enable getting them.

Use the System Function now() to Get the Data From the Last 30 Days in MySQL

If we want to get all records from the sales table, we can use the below query, which aids in displaying all the records of the sales table.

SQL Query:

select * from sales

We can get the last 30 days’ records by modifying the above SQL query.

SQL Query:

select * from sales
where order_date > now() - interval 30 day;

With the above query, we can select records where order_date falls after a past 30 days interval.

Output:

System function now() gets the latest DateTime value, and the interval clause calculates the date 30 days in the past.

The syntax of the interval clause is as below.

interval expr unit

1. expr - the quantity
2. unit - the unit for the amount interpreted (e.g., HOUR, DAY, or WEEK)
3. interval - keyword and the unit specifier

These are not case-sensitive and can perform in expressions by combining interval with the + or - operator.

Use current_date() to Get the Data From the Last 30 Days in MySQL

Instead of now(), we can use the current_date(). Below is the SQL query on how we are going to use that.

SQL Query:

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

Output:

In this query, the process is the same as the above SQL query, and here we are replacing now() with current_date().

Use the System Function date_sub() to Get the Data From the Last 30 Days in MySQL

Using the below SQL query will also help get records of 30 days.

SQL Query:

select * from sales
where  `order_date` >= date_sub(curdate(), interval 30 day)

Output:

According to the above, they have used two unique methods that differ from the above-discussed method.

Here they have used the function curdate() to find the current date while date_sub() is used to subtract 30 days from the calculated current DateTime.

As shown in the output, the SQL query extracts and displays the last 30 days’ data from the data set.

Use the between Command to Get the Data From the Last 30 Days in MySQL

The below query is a combination of the above methods. Differing from the above methods, here we are using the command BETWEEN in the SQL query.

SQL Query:

select date_format(order_date,'%Y/%m/%d') from sales
where order_date between now() - interval 30 day and now()

Output:

Using the between command enables prevention from getting future records and exactly outputs the records between the given period.

Here it extracts the records between the last 30 days to the latest day. At the earliest query, define the date format for easy access to get output.

Conclusion

Overall, we can get the records of the last 30 days and displays using the above methods. There may be more methods for getting the data records for the last 30 days with the combination of the above-discussed methods, but these are the most common methods for extracting the data records of the previous 30 days.