Should we use CAST for compare a date like on query below?
On this discussion mysql between operator with dates said it's better to convert your parameter STRING to DATE, but isn't mysql function in WHERE clause will make performance slower?
SELECT * from tableA WHERE datetimecolumn = CAST('2014-10-12' AS DATE)
use it this way:
SELECT *
from tableA
WHERE datetimecolumn = STR_TO_DATE('2014-10-12', '%Y-%m-%d');
documentation... http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date
okay.. so here's why.
mysql> select * from temp;
+---------------------+
| dt |
+---------------------+
| 2014-10-11 23:30:53 |
+---------------------+
2 rows in set (0.00 sec)
you could also do it this way... but you need a function. it won't hurt.
SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'
SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'
from here: https://stackoverflow.com/a/2758585/623952
this is what i ended up with... so I guess, if you know your exact datetime. use it.
mysql> select count(*) from temp;
+----------+
| count(*) |
+----------+
| 399998 |
+----------+
1 row in set (0.11 sec)
mysql> select count(*) from temp where temp_dt = date('2014-10-17');
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.15 sec)
mysql> select count(*) from temp where date_format(temp_dt, "%Y-%m-%d") = '2014-10-17';
+----------+
| count(*) |
+----------+
| 57590 |
+----------+
1 row in set (0.17 sec)
mysql> select count(*) from temp where substring(temp_dt,0,10) = '2014-10-17';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.14 sec)
mysql> select count(*) from temp where temp_dt = '2014-10-17 17:06:20';
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.12 sec)
