How to Efficiently Extract Date Values from a DateTime Column in MySQL?

Selecting Date from Datetime Column

When working with a datetime column containing both date and time information, you may need to extract the date component for filtering and querying purposes. Here's how you can achieve this:

Using MySQL's DATE() Function

The DATE() function returns only the date portion of a datetime value. To use this function, you can modify your query as follows:

SELECT * 
FROM data 
WHERE DATE(datetime) = '2009-10-20'
ORDER BY datetime DESC

Using LIKE Operator

Another way to select dates from a datetime column is to use the LIKE operator. The following query will also return rows where the date component matches the specified pattern:

SELECT * 
FROM data 
WHERE datetime LIKE '2009-10-20%' 
ORDER BY datetime DESC

However, using LIKE for date comparisons may have performance implications, as it requires MySQL to perform a full table scan. For optimal performance, consider using the DATE() function.

The above is the detailed content of How to Efficiently Extract Date Values from a DateTime Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!