How to Correctly Select Data Between Two Dates in MySQL?

Selecting Data Between Two Dates in MySQL

When working with date and time data in MySQL, it's common to need to select data that falls within a specific range of dates. However, when using the created_at column as a datetime value, the default interpretation is midnight for short dates.

Problem:

You may encounter a situation where a query like the following does not return a row, despite the row's created_at timestamp falling within the specified date range:

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

Solution:

The issue is that the query is actually selecting data with a range from '2011-12-01 00:00:00' to '2011-12-06 00:00:00'. To fix this, there are several approaches:

• Extend the end date range: Change the query to use the following range:

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

• Use DATE_ADD(): Calculate the end date by adding one day to the original 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 for readability:

SELECT `users`.* FROM `users` WHERE created_at BETWEEN('2011-12-01', DATE_ADD('2011-12-01', INTERVAL 7 DAY))

Using any of these approaches will correctly select data that falls within the specified date range, including rows with created_at timestamps such as '2011-12-06 10:45:36'.

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