How to Efficiently Retrieve MySQL Records from the Last 30 Days?

Retrieving Records within a 30-Day Range in MySQL

Users often encounter difficulties when attempting to retrieve records added to a MySQL database within a specific date range, particularly when utilizing date formatting. This article addresses this challenge by providing a comprehensive solution to retrieve records between today and the last 30 days, ensuring proper date formatting for display purposes.

The original query provided in the question encountered an issue where it failed to limit the selection to the last 30 days and retrieved all records instead. To correct this, it is crucial to apply the DATE_FORMAT function within the SELECT clause, rather than the WHERE clause. The corrected query is as follows:

SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

It's important to note that CURDATE() retrieves only the date portion, excluding the time. If your create_date column stores data as DATETIME with a non-zero time component, this query may not return today's records.

To address this, consider using NOW() instead:

SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

This modification ensures that the query returns records added within the last 30 days, including those added on the current day with any time component.

The above is the detailed content of How to Efficiently Retrieve MySQL Records from the Last 30 Days?. For more information, please follow other related articles on the PHP Chinese website!