比较 MySQL 中的日期忽略 DateTime 字段的时间部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10776539/
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
Comparing dates in MySQL ignoring time portion of a DateTime field
提问by Tiny
I need to compare dates in MySQL ignoring the time portion in a DateTimecolumn. I have tried the following SQL.
我需要比较 MySQL 中的日期而忽略DateTime列中的时间部分。我尝试了以下 SQL。
SELECT * FROM order_table where order_date=date_format('2012-05-03', '%Y-%m-%d');
It doesn't retrieve any row even though there is a date 2012-05-03 10:16:46in MySQL table. How can the time portion in the DateTimefield be ignored while comparing dates in MySQL?
即使2012-05-03 10:16:46MySQL 表中有日期，它也不会检索任何行。DateTime在 MySQL 中比较日期时如何忽略字段中的时间部分？
回答by Mark Byers
You could use the DATEfunction:
您可以使用该DATE功能：
SELECT col1, col2, ..., coln
FROM order_table
WHERE date(order_date) = '2012-05-03'
But this is more efficient, if your table is large and you have an index on order date:
但这会更有效率，如果您的表很大并且您有一个索引order date：
SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-03'
AND order_date < '2012-05-04'
回答by James Black
回答by deej
@Mark has got the good approach but just be careful that you will always have to calculate and find next day in that case. If you want to avoid that and still ignore time you could do following:
@Mark 有很好的方法，但请注意，在这种情况下，您将始终必须计算并找到第二天。如果您想避免这种情况并仍然忽略时间，您可以执行以下操作：
WHERE order_date >= '2012-05-27 00:00:00' AND order_date <= '2012-05-27 23:59:59'
I hope this makes sense.
我希望这是有道理的。
回答by lanzz
SELECT * FROM order_table WHERE date(order_date) = '2012-05-03';
回答by Kostia Shiian
回答by Erevodifosin
This is bit old .... but a correct answer also is using the date_format at the column order_date like below:
这有点旧......但正确的答案也是在 order_date 列中使用 date_format ，如下所示：
SELECT * FROM order_table where date_format(order_date, '%Y-%m-%d')='2012-05-03';
回答by shareef
In my case i had , DATE_FORMATand CONVERT_TZ
就我而言，我有 ,DATE_FORMAT和CONVERT_TZ
The only one worked for me is this
唯一对我有用的是这个
DATE_FORMAT(CONVERT_TZ(`order_item`.created, '+0:00', '+2:00'), '%d/%m/%Y %H:%i')
BETWEEN
('12/02/2018 01:42')
AND
('12/02/2018 10:51')
回答by Augustas
one of the following could be used when comparing dates in MySQL:
在 MySQL 中比较日期时可以使用以下之一：
DATEDIFF() Subtract two dates
TIMEDIFF() Subtract time
TIMESTAMPDIFF() Subtract an interval from a datetime expression
PERIOD_DIFF() Return the number of months between periods
for more on date functions MySQL documentation.
有关日期函数的更多信息 MySQL文档。
