How to select date from datetime column?
You can use MySQL's DATE() function:
WHERE DATE(datetime) = '2009-10-20'
You could also try this:
WHERE datetime LIKE '2009-10-20%'
See this answer for info on the performance implications of using LIKE.
Using WHERE DATE(datetime) = '2009-10-20' has performance issues. As stated here:
- it will calculate
DATE()for all rows, including those that don't match. - it will make it impossible to use an index for the query.
Use BETWEEN or >, <, = operators which allow to use an index:
SELECT * FROM data
WHERE datetime BETWEEN '2009-10-20 00:00:00' AND '2009-10-20 23:59:59'
Update: the impact on using LIKE instead of operators in an indexed column is high. These are some test results on a table with 1,176,000 rows:
- using
datetime LIKE '2009-10-20%'=> 2931ms - using
datetime >= '2009-10-20 00:00:00' AND datetime <= '2009-10-20 23:59:59'=> 168ms
When doing a second call over the same query the difference is even higher: 2984ms vs 7ms (yes, just 7 milliseconds!). I found this while rewriting some old code on a project using Hibernate.
You can format the datetime to the Y-M-D portion:
DATE_FORMAT(datetime, '%Y-%m-%d')