mysql 日期与 date_format 的比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13507642/
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 date comparison with date_format
提问by Doni Andri Cahyono
I googled and tried several ways to compare date but unfortunately didn't get the result as expected. I have current state of records like following:
我用谷歌搜索并尝试了几种比较日期的方法，但不幸的是没有得到预期的结果。我有如下记录的当前状态：
mysql> select date_format(date(starttime),'%d-%m-%Y') from data;
+-----------------------------------------+
| date_format(date(starttime),'%d-%m-%Y') |
+-----------------------------------------+
| 28-10-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 03-11-2012 |
| 03-11-2012 |
| 07-11-2012 |
| 07-11-2012 |
I would like to compare date and therefore do like this:
我想比较日期，因此这样做：
mysql> select date_format(date(starttime),'%d-%m-%Y') from data where date_format(date(starttime),'%d-%m-%y') >= '02-11-2012';
+-----------------------------------------+
| date_format(date(starttime),'%d-%m-%Y') |
+-----------------------------------------+
| 28-10-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 03-11-2012 |
| 03-11-2012 |
| 07-11-2012 |
| 07-11-2012 |
I believe that the result should not include '28-10-2012'. Any suggestion? Thanks in advance.
我认为结果不应该包括“28-10-2012”。有什么建议吗？提前致谢。
回答by Jon Skeet
Your format is fundamentally not a sortable one to start with - you're comparing strings, and the string "28-10-2012" isgreater than "02-11-2012".
您的格式根本不是什么排序一个开始-你是比较字符串，字符串“28-10-2012”是不是“2012年2月11日”更大。
Instead, you should be comparing dates as dates, and then only converting them into your target format for output.
相反，您应该将日期与日期进行比较，然后仅将它们转换为目标格式以进行输出。
Try this:
尝试这个：
select date_format(date(starttime),'%d-%m-%Y') from data
where date(starttime) >= date '2012-11-02';
(The input must always be in year-month-value form, as per the documentation.)
（根据文档，输入必须始终采用年-月-值形式。）
Note that if starttimeis a DATETIMEfield, you might want to consider changing the query to avoid repeated conversion. (The optimizer may well be smart enough to avoid it, but it's worth checking.)
请注意，如果starttime是DATETIME字段，您可能需要考虑更改查询以避免重复转换。（优化器可能足够聪明来避免它，但值得检查。）
select date_format(date(starttime),'%d-%m-%Y') from data
where starttime >= '2012-11-02 00:00:00';
(Note that it's unusual to format a date as d-m-Yto start with - it would be better to use y-M-din general, being the ISO-8601 standard etc. However, the above code does what you asked for in the question.)
（请注意，将日期格式化为一d-m-Y开始是不寻常的-y-M-d一般情况下使用会更好，例如 ISO-8601 标准等。但是，上面的代码可以满足您在问题中的要求。）
回答by wormhit
Use 2012-11-02 instead of 02-11-2012 and you will not need date_format() anymore
使用 2012-11-02 而不是 02-11-2012，您将不再需要 date_format()
回答by Gaurav Gupta
Use the following method :
使用以下方法：
public function dateDiff ($date1, $date2) {
/* Return the number of days between the two dates: */
return round(abs(strtotime($date1)-strtotime($date2))/86400);
}
/* end function dateDiff */
It will help!
我会帮你的！
