mysql比较两个时间字段,Mysql比较两个datetime字段

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.