How to Correctly Select MySQL Data Between Two Dates Including Time?

MySQL: Selecting Data between Two Dates

In MySQL, when working with datetime fields, it's essential to be aware of the nuances of date formatting. As you've discovered, using the date format '2011-12-06 10:45:36' without accounting for time can lead to unexpected results when trying to retrieve data between specific dates.

The Midnight Trap

The default short version of dates in MySQL interprets 00:00:00 as the start of a day. This means that your query:

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

was effectively becoming:

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

As a result, the item you were expecting, '2011-12-06 10:45:36', was excluded because its time component was outside the specified range.

Elegant Solutions

To address this issue, you have a few options:

• Extend the End Date: Change the query to include the following day to capture the entire range:

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

• Use DATE_ADD: Use the DATE_ADD function to add an interval to the end date:

SELECT `users`.* from `users` 
WHERE created_at >= '2011-12-01' 
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)

• Utilize the BETWEEN Operator: This operator provides a more concise way of specifying the date range:

SELECT `users`.* from `users` 
WHERE created_at BETWEEN('2011-12-01', date_add('2011-12-01', INTERVAL 7 DAY));

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