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