299

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?

Aris
  • 4,643
  • 1
  • 41
  • 38
mysqllearner
  • 13,523
  • 15
  • 43
  • 43

9 Answers9

548

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.

Luke
  • 3,985
  • 1
  • 20
  • 35
Priyank Bolia
  • 14,077
  • 14
  • 61
  • 82
  • 2
    tried this one : Where DATE(datetime) = '2009-10-20', it works – mysqllearner Nov 18 '09 at 08:48
  • 54
    The first one is very slow and ignores the indexes. It is better to have LIKE 'date%' – kachar Feb 22 '13 at 10:03
  • WHERE DATE(datetime) LIKE '%keyword%' its working, thanks – silvia zulinka Oct 31 '16 at 09:45
  • 2
    I believe this does not work as expected if the Rails timezone is something other than UTC. This is because DATE() resolves the column value to UTC. So a date such as "Fri, 30 Dec 2016 00:00:00 SGT +08:00" which we expect to find if we queried for "2016-12-30" will not be found. Why? Because DATE() will resolve it to its UTC equivalent before doing the comparism, which is "2016-12-29 16:00:00 UTC". Correct me if I am wrong as this was also a gotcha to me until recently. – Tikiboy Jan 04 '17 at 05:11
  • WHERE DATE(datetime) = '{?sdate} works perfectly for a Crystal Reports parameter! Nice work! – MrrMan Apr 26 '17 at 11:08
  • It is a performance killer and also floors the input date thereby considering everything between '2009-10-20 00:00:00' and '2009-10-20 23:59:59' – Kevin Prasanna R R Aug 14 '19 at 12:45
  • @Tikiboy - Rails stores DateTime and Time values in UTC (by default, out-of-the-box). So, if you haven't changed that default, simply make sure that the date you query for ("2016-12-30" in your example) is certain to be taken from a DateTime or Time object (use .to_date) that is in UTC. Problem solved. – TomDogg Apr 22 '21 at 17:30
131

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.

IvanRF
  • 7,115
  • 5
  • 47
  • 71
  • Interesting, if MySql implementation automatically converts `DATE(datetime)` to `BETWEEN AND ` behine the scenes we get short statement and cool performance. Why not? Why they did not do this? :) – Cherry Dec 30 '16 at 05:04
  • Because something like `DATE(datetime) in (:list)` doesnt work with `BETWEEN` – Tim Jun 06 '18 at 15:54
32

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

DATE_FORMAT(datetime, '%Y-%m-%d')
Prusprus
  • 7,987
  • 9
  • 42
  • 57
  • 3
    And this way you can get only the time part if needed: `DATE_FORMAT(datetime, '%H:%i:%S')` – Metafaniel Oct 03 '17 at 22:38
  • this works great formating the datetime but in return the database will return a new array to set the name for it just ad the desired name in the end DATE_FORMAT(columnName, '%Y-%m-%d') arrayName for more details check documentations https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format – Ridha Rezzag Jan 18 '19 at 05:35
19

Though all the answers on the page will return the desired result, they all have performance issues. Never perform transformations on fields in the WHERE clause (including a DATE() calculation) as that transformation 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.

dotancohen
  • 30,064
  • 36
  • 138
  • 197
  • 1
    And, using your performance argument, are the ">=" and "<" equally calculated on each row of the table, or the SQL server just "magically" knows if a number is greater than other? :-) – Javier Guerrero Oct 17 '17 at 07:55
  • @JavierGuerrero: My example compares two constants: One from the table and a string literal. – dotancohen Oct 17 '17 at 11:10
  • The "one from the table" is not a constant, it varies for each row, so the same calculation has to be done for each row (and also, both have to be converted to epoch time to perform the comparison) – Javier Guerrero Oct 18 '17 at 11:23
14

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 |
+----------------------+
Siraj Alam
  • 9,217
  • 9
  • 53
  • 65
5

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))
maya
  • 67
  • 2
  • 9
Michel van Engelen
  • 2,791
  • 2
  • 29
  • 45
5

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'  && date(datetime )  <= '2009-10-20'
Raja Rama Mohan Thavalam
  • 8,131
  • 2
  • 31
  • 30
0

I tried date(tscreated) = '2022-06-04' on a large record set. My tscreated is indexed. It took 42 seconds.

I then tried tscreated >= '2022-06-04' and tscreated < '2022-06-05' and the time was .094 sec.

I realize that the record set might be in memory the second time, but I also believe that the date function negates the value of the index.

Ed Greenberg
  • 209
  • 3
  • 12
-6

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

AymDev
  • 6,626
  • 4
  • 29
  • 52
imran
  • 169
  • 1
  • 1
  • 9