How to select date from datetime column?

Question

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?

Shumpert

Answer 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.

Russia

tried this one : Where DATE(datetime) = '2009-10-20', it works – Shumpert

The first one is very slow and ignores the indexes. It is better to have LIKE 'date%' – Amalgamation

WHERE DATE(datetime) LIKE '%keyword%' its working, thanks – Spyglass

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. – Pentastyle

WHERE DATE(datetime) = '{?sdate} works perfectly for a Crystal Reports parameter! Nice work! – Slusher

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' – Vanhoose

@Pentastyle - 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. – Mourning

Answer 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.

Alix

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? :) – Attic

Because something like DATE(datetime) in (:list) doesnt work with BETWEEN – Fatalism

Answer 3

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

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

Kalle

And this way you can get only the time part if needed: DATE_FORMAT(datetime, '%H:%i:%S') – Tag

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 dev.mysql.com/doc/refman/8.0/en/… – Frobisher

Answer 4

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.

Placeeda

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? :-) – Donnadonnamarie

@JavierGuerrero: My example compares two constants: One from the table and a string literal. – Placeeda

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) – Donnadonnamarie

Answer 5

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 |
+----------------------+

Simard

Answer 6

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))

Boracic

Answer 7

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'

Magnien

Answer 8

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.

Emanative

Answer 9

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

Brunt