MySQL 选择日期时间大于指定日期的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17659577/
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
Select records where datetime is greater than the specified date
提问by colourtheweb
Problem
问题
I am trying to fetch all the records from table where date_timefield is greater than 'Thu, 11 Jul 2013' by running the below mentioned query. Value in the date_time field is stored in this format => Thu, 11 Jul 2013 08:29:37. Any help will be great.
我试图通过运行下面提到的查询，从date_time字段大于“ Thu, 11 Jul 2013”的表中获取所有记录。date_time 字段中的值以这种格式存储=> Thu, 11 Jul 2013 08:29:37。任何帮助都会很棒。
Datatype of field date_time is varchar
字段 date_time 的数据类型是varchar
Query
询问
SELECT * FROM table_name
WHERE username = '[email protected]'
AND STR_TO_DATE(date_time, '%a, %e %b %Y %H:%i:%s') >= 'Thu, 11 Jul 2013 00:00:00';
回答by Mike Brant
Here is yet another great example of why you should implement date/time fields in MySQL using the date, datetime, or timestamp field types and let your application deal with how to format the date for output.
这是另一个很好的例子，说明为什么应该使用日期、日期时间或时间戳字段类型在 MySQL 中实现日期/时间字段，并让您的应用程序处理如何格式化输出日期。
Right now you are going to need to do something like:
现在，您将需要执行以下操作：
SELECT * FROM table_name
WHERE username = '[email protected]'
AND STR_TO_DATE(date_time, '%a, %e %b %Y %H:%i:%s') >= STR_TO_DATE('Thu, 11 Jul 2013 00:00:00', '%a, %e %b %Y %H:%i:%s');
This query will not be able to use any index you have on your date_timefield, so the query will be very inefficient. It will need to perform a full table scan, converting the value of each row in order to make the comparison.
此查询将无法使用您在date_time字段上的任何索引，因此查询效率将非常低下。它将需要执行全表扫描，转换每一行的值以进行比较。
What you should be doing is:
你应该做的是：
SELECT * FROM table_name
WHERE username = '[email protected]'
AND date_time >= STR_TO_DATE('Thu, 11 Jul 2013 00:00:00', '%a, %e %b %Y %H:%i:%s');
Here if you have your field in the MySQL datetime format, you just need to convert the input to a this format for matching. Since your field data is already in this format, you will be able to utilize an index for the search.
在这里，如果您的字段采用 MySQL 日期时间格式，则只需将输入转换为此格式以进行匹配。由于您的字段数据已经采用这种格式，您将能够利用索引进行搜索。
回答by Barranka
You are trying to compare a date with a String.
您正在尝试将日期与字符串进行比较。
The str_to_datefunction application is correct, but you are not comparing to a date.
该str_to_date功能的应用程序是正确的，但你是不是比较的日期。
The right way to do it is:
正确的做法是：
select * from yourTable
where STR_TO_DATE(date_time, '%a, %e %b %Y %H:%i:%s') >= '2013-07-11 00:00:00'
Notice that the date format is YYYY-MM-DD HH:mm:ss(which is MySQL default date format).
请注意，日期格式为YYYY-MM-DD HH:mm:ss（这是 MySQL 默认日期格式）。
Of course, you can also compare to str_to_dateresults:
当然，你也可以对比str_to_date结果：
... where STR_TO_DATE(date_time, '%a, %e %b %Y %H:%i:%s') >= STR_TO_DATE('Thu, 11 Jul 2013 00:00:00', '%a, %e %b %Y %H:%i:%s')
