Mysql比较两个datetime字段 [英] Mysql Compare two datetime fields

问题描述

我想比较两个日期与时间，我想要所有的结果 from tbl where date1> date2

 选择* From temp where mydate> '2009-06-29 04:00:44'; 
  

但它只是比较日期而不是时间。它给我所有今天日期的结果集

 '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-6-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'
  

提前感谢！

解决方案

您想要显示的查询是：

  SELECT * FROM temp WHERE mydate> '2009-06-29 16:00:44'; 
  

04:00:00是凌晨4点，因此您显示的所有结果。

如果您想在下午4点后显示所有内容，您需要在查询中使用正确的（24小时）符号。 p>

要使事情更清晰，请尝试：

  SELECT mydate， DATE_FORMAT（mydate，'％r'）FROM temp; 
  

这将显示日期和12小时的时间。

I want to compare two dates with time, I want all the results 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!

解决方案

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 after that, which is correct.

If you want to show everything after 4PM, you need to use the correct (24hr) notation in your query.

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.

这篇关于Mysql比较两个datetime字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！