How to Efficiently Retrieve MySQL Records Based on Date Only, Ignoring Time?

Retrieve Records Based on Date Only, Excluding Time

Problem:

You have a MySQL table with a datetime column and require a way to select records that fall within a specific day, without considering the time component.

Solution:

To achieve this, avoid using DATE() function in your WHERE clause, as it hampers performance and prevents index usage. Instead, utilize the BETWEEN operator to specify a day range that encompasses the desired date.

SELECT * FROM tablename
WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59'

Alternatively, for MySQL versions that may have limitations with specifying '23:59:59' for the day end, you can employ the following query:

SELECT * FROM tablename
WHERE columname >= '2012-12-25 00:00:00'
AND columname < '2012-12-26 00:00:00'

This approach ensures that you only retrieve records for the specified day, without any influence from the time component.

The above is the detailed content of How to Efficiently Retrieve MySQL Records Based on Date Only, Ignoring Time?. For more information, please follow other related articles on the PHP Chinese website!