mysql – How to select date from datetime column?

The Question :

251 people think this question is useful

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?

The Question Comments :

The Answer 1

477 people think this answer is useful

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.

The Answer 2

104 people think this answer is useful

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.

The Answer 3

26 people think this answer is useful

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

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

The Answer 4

15 people think this answer is useful

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.

The Answer 5

11 people think this answer is useful

Here are all formats

Say this is the column that contains the datetime value, table data.

+--------------------+
| date_created       |
+--------------------+
| 2018-06-02 15:50:30|
+--------------------+


mysql> select DATE(date_created) from data;
+--------------------+
| DATE(date_created) |
+--------------------+
| 2018-06-02         |
+--------------------+


mysql> select YEAR(date_created) from data;
+--------------------+
| YEAR(date_created) |
+--------------------+
|               2018 |
+--------------------+


mysql> select MONTH(date_created) from data;
+---------------------+
| MONTH(date_created) |
+---------------------+
|                   6 |
+---------------------+


mysql> select DAY(date_created) from data;
+-------------------+
| DAY(date_created) |
+-------------------+
|                 2 |
+-------------------+


mysql> select HOUR(date_created) from data;
+--------------------+
| HOUR(date_created) |
+--------------------+
|                 15 |
+--------------------+


mysql> select MINUTE(date_created) from data;
+----------------------+
| MINUTE(date_created) |
+----------------------+
|                   50 |
+----------------------+


mysql> select SECOND(date_created) from data;
+----------------------+
| SECOND(date_created) |
+----------------------+
|                   31 |
+----------------------+

The Answer 6

5 people think this answer is useful

You can use:

DATEDIFF ( day , startdate , enddate ) = 0

Or:

DATEPART( day, startdate ) = DATEPART(day, enddate)
AND 
DATEPART( month, startdate ) = DATEPART(month, enddate)
AND
DATEPART( year, startdate ) = DATEPART(year, enddate)

Or:

CONVERT(DATETIME,CONVERT(VARCHAR(12), startdate, 105)) = CONVERT(DATETIME,CONVERT(VARCHAR(12), enddate, 105))

The Answer 7

5 people think this answer is useful

simple and best way to use date function

example

SELECT * FROM 
data 
WHERE date(datetime) = '2009-10-20' 

OR

SELECT * FROM 
data 
WHERE date(datetime ) >=   '2009-10-20'  &amp;&amp; date(datetime )  <= '2009-10-20'

The Answer 8

-5 people think this answer is useful

Well, using LIKE in statement is the best option WHERE datetime LIKE '2009-10-20%' it should work in this case

Add a Comment