MySQL 将 DATE 字符串与来自 DATETIME 字段的字符串进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2758486/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL compare DATE string with string from DATETIME field
提问by Manny Calavera
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)?
我有一个问题：是否可以通过将一个 DATE 字符串“2010-04-29”与存储为 DATETIME (2010-04-29 10:00) 的字符串进行比较来从 MySQL 数据库中进行选择？
I have one date picker that filters data and I would like to query the table by the DATETIME field like this:
我有一个过滤数据的日期选择器，我想通过 DATETIME 字段查询表，如下所示：
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".
...我想获取 DATETIME 值为“2010-04-29 10:00”的行。
Any suggestions? Thanks.
有什么建议？谢谢。
回答by David
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.
仅供参考，我有一个 200 万条记录表，我运行了一个类似的查询。Salils 回答用了 4.48 秒，上面用了 2.25 秒。
So if the table is BIG I would suggest this rather.
所以如果桌子很大，我会建议这样做。
回答by XL_
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:
如果要选择 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
因为 MySQL 不能直接比较 DATE 和 DATETIME。MySQL 的作用是将给定的 DATE 文字扩展为时间“00:00:00”。所以你的条件变成
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.
第一个错误的可能性很小 - 当您的 DATETIME 列使用亚秒分辨率并且在 23:59:59 + epsilon 有约会时。一般来说，我建议使用第二种变体。
Both variants can use an index on startTime which will become important when the table grows.
两种变体都可以在 startTime 上使用索引，这在表增长时会变得很重要。
回答by Salil
SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'"
OR
或者
SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'
回答by R T
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).
这用于 mysql 中的日期时间格式，使用DATE_FORMAT(date,format).
回答by Sarath
SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29';
it helps , you can convert the values as DATEbefore comparing.
它有帮助，您可以像DATE比较之前一样转换值。
回答by SGAmpere
You can cast the DATETIME field into DATE as:
您可以将 DATETIME 字段转换为 DATE：
SELECT * FROM `calendar` WHERE CAST(startTime AS DATE) = '2010-04-29'
This is very much efficient.
这是非常有效的。
回答by Fletcher Moore
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.
如果您想在 MySQL 日期之后或之前查找某些内容，您还可以在 MySQL 日期上使用比较运算符。这是因为它们的编写方式（最大值到最小值带前导零）使得简单的字符串排序可以正确地对它们进行排序。
