How to Properly Select Data Between Two Dates in MySQL?

Selecting Data from a MySQL Database Between Two Dates

In MySQL, retrieving data within a specific date range presents a common challenge when storing dates as datetime values. Using >= and <= operators might not always yield the desired results due to the default interpretation of midnight as the start of a date.

Consider the scenario where you want to select records where the created_at column falls between '2011-12-01' and '2011-12-06.' Running the following query will surprisingly exclude the expected record that occurred at '2011-12-06 10:45:36':

SELECT `users`.* FROM `users` 
WHERE created_at >= '2011-12-01' 
AND created_at <= '2011-12-06'

The reason is that MySQL interprets the end date as midnight ('2011-12-06 00:00:00'), effectively excluding records created later that day. To select the intended record, modify the end date to '2011-12-07':

SELECT `users`.* FROM `users` 
WHERE created_at >= '2011-12-01' 
AND created_at <= '2011-12-07'

Alternatively, use DATE_ADD() to add days to the start date, creating an interval that includes the desired end date:

SELECT `users`.* FROM `users` 
WHERE created_at >= '2011-12-01' 
AND created_at <= DATE_ADD('2011-12-01', INTERVAL 7 DAY)</p>
<p>For improved readability, the BETWEEN operator can be utilized:</p>
<pre class="brush:php;toolbar:false">SELECT `users`.* FROM `users` 
WHERE created_at BETWEEN('2011-12-01', DATE_ADD('2011-12-01', INTERVAL 7 DAY))

By adjusting the end date or employing the DATE_ADD() function, you can accurately select data from the specified date range, ensuring that all records created on the intended end date are captured.

The above is the detailed content of How to Properly Select Data Between Two Dates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!