122

I have a question: Is it possible to select from a MySQL database by comparing one DATE string "2010-04-29" against strings that are stored as DATETIME (2010-04-29 10:00)?

I have one date picker that filters data and I would like to query the table by the DATETIME field like this:

SELECT * FROM `calendar` WHERE startTime = '2010-04-29'"

...and I would like to get the row that has the DATETIME value of "2010-04-29 10:00".

Any suggestions? Thanks.

w5m
  • 2,286
  • 3
  • 34
  • 46
Manny Calavera
  • 6,815
  • 20
  • 60
  • 85

6 Answers6

210

Use the following: 

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

Just for reference I have a 2 million record table, I ran a similar query. Salils answer took 4.48 seconds, the above took 2.25 seconds.

So if the table is BIG I would suggest this rather.

David
  • 3,927
  • 6
  • 30
  • 48
  • 12
    The first answer is so slow, because it has to format each datetime into a string before comparison. Your is better, because it directly compares only the date part from the field, but it still cannot use index (tested on mysql 5.1) – Marki555 Feb 20 '14 at 12:54
  • 2
    If performance is an issue it might be worth considering storing the date and time part separately, so that an INDEX can be placed on the date part. – Thijs Riezebeek Dec 16 '15 at 12:04
  • 2
    This query will not be able to use index if `startTime` is indexed. – Zamrony P. Juhara Dec 03 '19 at 02:30
57

If you want to select all rows where the DATE part of a DATETIME column matches a certain literal, you cannot do it like so:

WHERE startTime = '2010-04-29'

because MySQL cannot compare a DATE and a DATETIME directly. What MySQL does, it extends the given DATE literal with the time '00:00:00'. So your condition becomes 

WHERE startTime = '2010-04-29 00:00:00'

Certainly not what you want!

The condition is a range and hence it should be given as range. There are several possibilities:

WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'
WHERE startTime >= '2010-04-29' AND startTime < ('2010-04-29' + INTERVAL 1 DAY)

There is a tiny possibility for the first to be wrong - when your DATETIME column uses subsecond resolution and there is an appointment at 23:59:59 + epsilon. In general I suggest to use the second variant.

Both variants can use an index on startTime which will become important when the table grows.

XL_
  • 679
  • 5
  • 3
  • 10
    David's version is not good, it cannot use indexes. To use an index, you have to filter on the column directly, not on a result of a function (`date()`, `year()`, `datediff()` or any similar) – Marki555 Feb 20 '14 at 12:56
  • I was looking for how mysql converts the date to datetime , thanks for clearing it adds `00:00:00` . now my results makes sense – Accountant م Jan 18 '17 at 00:42
  • 1
    This is correct fast solution compared to other answers which cannot use index if available – Zamrony P. Juhara Dec 03 '19 at 02:32
28
SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'"

OR 

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'
Salil
  • 46,566
  • 21
  • 122
  • 156
  • 30
    This is a terrible query. Avoid this at all costs. If there is an index on startTime it cannot be used by this query, since it's applying a function to the column rather than using it directly. That means any query like this will require a full table scan. On a large table this will mean an extremely slow query. – steveayre May 09 '13 at 12:52
  • I agree, his answer is **wrong** - the correct answer was posted by David below. – mindplay.dk Jul 18 '13 at 13:40
  • 1
    Please, use the answer from David, because it's way more fast. This one is valid, but not good for the speed... – xarlymg89 Jul 24 '13 at 15:17
  • 2
    @CarlosAlbertoMartínezGadea This version is slow because it has to format each value to string before comparison... David's version does not need it, but still cannot use indexes, so is not optimal. See answer from XL_ for a version which can use index. Unfortunately there is no better way to do it properly in mysql – Marki555 Feb 20 '14 at 12:59
  • 1
    I downvoted this answer because the query cannot use an index, while @XL_'s answer does. – pedromanoel Jun 11 '14 at 16:45
3
SELECT * FROM sample_table WHERE last_visit = DATE_FORMAT('2014-11-24 10:48:09','%Y-%m-%d %H:%i:%s')

this for datetime format in mysql using DATE_FORMAT(date,format).

R T
  • 4,369
  • 3
  • 38
  • 49
0

You can cast the DATETIME field into DATE as:

SELECT * FROM `calendar` WHERE CAST(startTime AS DATE) = '2010-04-29'

This is very much efficient.

SGAmpere
  • 21
  • 3
-6
SELECT * FROM `calendar` WHERE startTime like '2010-04-29%'

You can also use comparison operators on MySQL dates if you want to find something after or before. This is because they are written in such a way (largest value to smallest with leading zeros) that a simple string sort will sort them correctly.

Fletcher Moore
  • 13,558
  • 11
  • 40
  • 58