JIYIK CN >

This article describes how to use SQL query to obtain the data records of the last 30 days from the database.

Import data into the database

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

SQL query:

create table sales(order_date date, sale int);

The above syntax creates a sales table with two columns: order_date and sale. The types of order_date and sale are date and int respectively.

Next we import some data into the table by manual entry.

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:

With the data imported, we can now extract the data as needed.

This article will introduce three methods to get the latest 30 days of data from the database. Although there is no built-in function in MySQL to get the latest 30 days of records, you can get them using the following SQL query.

Use the system function now() in MySQL to get the data for the last 30 days

If we want to get all the records from sales table, we can use the following query, which helps to display all the records of sales table.

SQL query:

select * from sales

We can modify the above SQL query to get the records of the last 30 days.

SQL query:

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

With the above query, we can select the records where order_date is after the last 30 days interval.

Output:

The system function now() obtains the latest DateTime value, and the interval clause calculates the date of the past 30 days.

The syntax of the interval clause is as follows.

interval expr unit

• expr - quantity
• unit - the unit in which the quantity is interpreted (for example, HOUR, DAY, or WEEK)
• interval - keyword and unit specifier

These are case-insensitive and can be performed in expressions by combining the interval with the +or operator.-

Use current_date() in MySQL to get data for the last 30 days

We can use current_date() instead of now(). Here is the SQL query on how we will use it.

SQL query:

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

Output:

In this query, the process is the same as in the above SQL query, here we replace now() with current_date().

Use the system function date_sub() in MySQL to get the data of the last 30 days

Using the following SQL query will also help in getting 30 days records.

SQL query:

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

Output:

Based on the above, they used two unique methods that were different from the ones mentioned above.

Here, they use 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 of data from the dataset.

Use the between command in MySQL to get the data for the last 30 days

The following query is a combination of the above methods. The difference from the above methods is that here we use 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:

Use the between command to prevent future records from being fetched and output exactly the records between the given time periods.

Here it extracts the records between the last 30 days to the latest day. In the earliest query, the date format is defined to facilitate getting the output.

Summarize

In general, we can use the above methods to obtain the records and display of the last 30 days. There may be more methods to combine the above methods to obtain the data records of the last 30 days, but these are the most commonly used methods to extract the data records of the previous 30 days.