Mysql 比较两个日期时间字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1058031/
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 two datetime fields
提问by MySQL DBA
I want to compare two dates with time, I want all the results from tbl where date1 > date2
我想用时间比较两个日期，我想要所有的结果 from tbl where date1 > date2
Select * From temp where mydate > '2009-06-29 04:00:44';
but it is just comparing dates not time. it is giving me all the result set of today's date
但它只是比较日期而不是时间。它给了我今天日期的所有结果集
'2009-06-29 11:08:57'
'2009-06-29 11:14:35'
'2009-06-29 11:12:38'
'2009-06-29 11:37:48'
'2009-06-29 11:52:17'
'2009-06-29 12:12:50'
'2009-06-29 12:13:38'
'2009-06-29 12:19:24'
'2009-06-29 12:27:25'
'2009-06-29 12:28:49'
'2009-06-29 12:35:54'
'2009-06-29 12:36:54'
'2009-06-29 12:49:57'
'2009-06-29 12:58:04'
'2009-06-29 04:13:20'
'2009-06-29 04:56:19'
'2009-06-29 05:00:23'
'2009-06-29 05:04:26'
'2009-06-29 05:08:17'
'2009-06-29 05:26:57'
'2009-06-29 05:29:06'
'2009-06-29 05:32:11'
'2009-06-29 05:52:07'
Thanks in advance!
提前致谢！
回答by Jeremy Smyth
The query you want to show as an example is:
您想作为示例显示的查询是：
SELECT * FROM temp WHERE mydate > '2009-06-29 16:00:44';
04:00:00 is 4AM, so all the results you're displaying come afterthat, which is correct.
04:00:00 是凌晨 4 点，所以您显示的所有结果都在此之后，这是正确的。
If you want to show everything after 4PM, you need to use the correct (24hr) notation in your query.
如果您想在下午 4 点之后显示所有内容，则需要在查询中使用正确的（24 小时）表示法。
To make things a bit clearer, try this:
为了让事情更清楚一点，试试这个：
SELECT mydate, DATE_FORMAT(mydate, '%r') FROM temp;
That will show you the date, and its 12hr time.
这将显示日期及其 12 小时时间。
回答by Anjuman
You can use the following SQL to compare both date and time -
您可以使用以下 SQL 来比较日期和时间 -
Select * From temp where mydate > STR_TO_DATE('2009-06-29 04:00:44', '%Y-%m-%d %H:%i:%s');
Attached mysql output when I used same SQL on same kind of table and field that you mentioned in the problem-
当我在问题中提到的相同类型的表和字段上使用相同的 SQL 时附加 mysql 输出 -
It should work perfect.
它应该工作完美。
回答by Sampson
Your query apparently returned all correct dates, even considering the time.
您的查询显然返回了所有正确的日期，即使考虑到时间。
If you're still not happy with the results, give DATEDIFF a shot and look for negaive/positive results between the two dates.
如果您仍然对结果不满意，请尝试 DATEDIFF 并在两个日期之间寻找负面/正面结果。
Make sure your mydatecolumn is a datetimetype.
确保您的mydate列是一种datetime类型。
回答by Erik
Do you want to order it?
您要订购吗？
Select * From temp where mydate > '2009-06-29 04:00:44' ORDER BY mydate;
