How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00

I would like to extract date from datetime and write a query like:

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

Is the following the best way to do it?

SELECT * FROM 
data 
WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59')
ORDER BY datetime DESC

This however returns an empty resultset. Any suggestions?

Solution 1:

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.

Solution 2:

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.

Solution 3:

You can format the datetime to the Y-M-D portion:

DATE_FORMAT(datetime, '%Y-%m-%d')

Solution 4:

Though all the answers on the page will return the desired result, they all have performance issues. Never perform calculations on fields in the WHERE clause (including a DATE() calculation) as that calculation must be performed on all rows in the table.

The BETWEEN ... AND construct is inclusive for both border conditions, requiring one to specify the 23:59:59 syntax on the end date which itself has other issues (microsecond transactions, which I believe MySQL did not support in 2009 when the question was asked).

The proper way to query a MySQL timestamp field for a particular day is to check for Greater-Than-Equals against the desired date, and Less-Than for the day after, with no hour specified.

WHERE datetime>='2009-10-20' AND datetime<'2009-10-21'

This is the fastest-performing, lowest-memory, least-resource intensive method, and additionally supports all MySQL features and corner-cases such as sub-second timestamp precision. Additionally, it is future proof.