How to Retrieve Data from the Previous Month Using SQL?

Retrieve Rows from the Previous Month Using SQL

As you have database entries timestamped with date and time, you may encounter the need to retrieve data from a specific time range. Let's explore how you can query your database to extract all rows from the previous month.

To start, you have a table with a date_created column, which retains the date and time of each row's creation in the "YYYY-MM-DD HH:MM:SS" format. Your goal is to isolate rows that belong to the month preceding the current one.

The following SQL query accomplishes this task:

SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

Let's break down this query:

• CURRENT_DATE - INTERVAL 1 MONTH subtracts one month from the current date.
• YEAR(CURRENT_DATE - INTERVAL 1 MONTH) extracts the year from the result of the subtraction.
• MONTH(CURRENT_DATE - INTERVAL 1 MONTH) extracts the month from the result of the subtraction.
• The WHERE clause compares the year and month of date_created to the year and month extracted from the previous month, ensuring that only rows from the previous month are retrieved.

By executing this query, you can efficiently retrieve all rows created within the previous month, regardless of the current date. This technique allows you to easily filter your database based on time-based criteria.

The above is the detailed content of How to Retrieve Data from the Previous Month Using SQL?. For more information, please follow other related articles on the PHP Chinese website!