How to Efficiently Retrieve Records within the Last 30 Days in MySQL?

MySQL Query Optimization: Retrieving Records within a Time Range

In the previous discussion, a user sought guidance on retrieving records added to a database within the last 30 days, with a specific requirement to display dates in the mm/dd/yy format. The original query provided encountered an issue where it failed to limit the results to the desired time frame.

Upon examination, it was determined that the issue stemmed from the erroneous placement of the DATE_FORMAT function. To achieve the desired filtering, the function should be applied in the SELECT clause rather than the WHERE clause. Here is the corrected query:

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

Furthermore, it's important to note that CURDATE() only returns the date portion of the timestamp. If the create_date column contains a DATETIME value with a non-zero time component, the original query would exclude today's records. To address this, NOW() can be used instead:

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

With these corrections, the query will correctly retrieve records that meet the specified time range and format requirements.

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