MySQL compare DATE string with string from DATETIME field

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.

243513 次浏览
小开 
SELECT * FROM `calendar` WHERE startTime like '2010-04-29%'

如果希望在 MySQL 日期之后或之前查找某些内容，还可以在 MySQL 日期上使用比较运算符。这是因为它们的编写方式(从最大值到最小值，前导零)使得简单的字符串排序可以正确地对它们进行排序。

小开 
SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'"

或者

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

如果希望选择 DATETIME 列的 DATE 部分与某个文字匹配的所有行，则不能这样做:

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'

当然不是你想要的！

条件是一个范围，因此它应该作为范围给出。有几种可能性:

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.

这两个变量都可以在 startTime 上使用索引，当表增长时索引将变得非常重要。

小开
最佳答案

使用以下方法:

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

仅供参考，我有一个200万记录表，我运行了类似的查询。 Salils 的答案用了4.48秒，上面的用了2.25秒。

因此，如果桌子很大，我宁愿建议这样做。

小开 
SELECT * FROM sample_table WHERE last_visit = DATE_FORMAT('2014-11-24 10:48:09','%Y-%m-%d %H:%i:%s')

使用 DATE_FORMAT(date,format)获得 mysql 中的日期时间格式。

小开

您可以将 DATETIME 字段强制转换为 DATE，如下所示:

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

This is very much efficient.


提交答案