MySQL 如何使用mysql测试日期和日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1934352/
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
how to test date and datetime with mysql
提问by u231664
My table is using a datetime (YYYY-MM-DD HH:MM:SS)and i need to display today's entries.
我的表正在使用日期时间(YYYY-MM-DD HH:MM:SS)，我需要显示今天的条目。
my code is only :
我的代码只是：
SELECT *
FROM table
WHERE date = '$date'
ORDER BY score DESC
with
和
$date = date("Y-m-d");
well, as expected it doesnt work :| you guys have a solution here ?
好吧，正如预期的那样，它不起作用：| 你们这里有解决方案吗？
回答by Daniel Vassallo
Following from Pascal Martin, you could extract the date part from the date+time field:
继 Pascal Martin 之后，您可以从日期+时间字段中提取日期部分：
SELECT * FROM table WHERE DATE(date) = '2009-12-19'
Source: MySQL - Date and Time Functions
Be aware however, that this query will not use an index on your date+time field, if you will be having one. (Stack Overflow: How does one create an index on the date part of DATETIME field in MySql)
但是请注意，此查询不会在您的日期+时间字段上使用索引，如果您有索引的话。（堆栈溢出：如何在 MySql 中的 DATETIME 字段的日期部分创建索引）
回答by Pascal MARTIN
Your date is "2009-12-19" (or something like that, depending on the day), which is interpreted as "2009-12-19 00:00:00".
您的日期是“2009-12-19”（或类似的日期，具体取决于日期），它被解释为“2009-12-19 00:00:00”。
In your database, you probably don't have any date that's exactly equal to that one, by the second : your dates are like "2009-12-19 12:15:32".
在您的数据库中，您可能没有任何与该日期完全相同的日期，第二个：您的日期就像“2009-12-19 12:15:32”。
A solution is to compare like this :
一个解决方案是这样比较：
select *
from table
where date >= '2009-12-19'
and date < '2009-12-20'
Which will be interpreted as :
这将被解释为：
select *
from table
where date >= '2009-12-19 00:00:00'
and date < '2009-12-20 00:00:00'
And, if you don't want to do the math to get the date of the following date, you can use the adddatefunction :
而且，如果您不想进行数学运算来获得下一个日期的日期，则可以使用该adddate函数：
select *
from table
where date >= '2009-12-19'
and date < adddate('2009-12-19', interval 1 day)
So, in your case, something like this should do the trick :
所以，在你的情况下，这样的事情应该可以解决问题：
select *
from table
where date >= '$date'
and date < adddate('$date', interval 1 day)
order by score desc
回答by DisgruntledGoat
You probably want to format the data when you select it:
您可能希望在选择数据时格式化数据：
SELECT *, DATE_FORMAT(date, '%Y-%m-%d') AS dateformat FROM table
WHERE dateformat = '$date' ORDER BY score DESC
回答by amrinder
You are comparing datetime and date expression, that is why its not working. Use Date() method to return the date part from datetime and then do the comparison. WHERE DATE(date) = '$date' should do. You might have to use aliases to handle this name collision.
您正在比较日期时间和日期表达式，这就是它不起作用的原因。使用 Date() 方法从 datetime 返回日期部分，然后进行比较。WHERE DATE(date) = '$date' 应该可以。您可能必须使用别名来处理此名称冲突。
