60

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'
starball
  • 20,030
  • 7
  • 43
  • 238
MySQL DBA
  • 5,692
  • 21
  • 54
  • 71
  • 17
    There's no result there that's less than '2009-06-29 04:00:44';, so it seems your code is working, or am I missing something? – Jeremy Smyth Jun 29 '09 at 12:31
  • I want these dates as Output not before that '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' – MySQL DBA Jun 29 '09 at 12:35
  • 1
    Um... You don't have any visible from before that. It seems you're suggesting '2009-06-29 11:08:57' comes before '2009-06-29 04:00:44'. 4AM comes before 11am, and therefore you're seeing that 11am is greater than 4am. Again, am I missing something? – Jeremy Smyth Jun 29 '09 at 12:39
  • 2009-06-29 04:00:44' this is 4PM, so i dont want 11AM records. can i get them – MySQL DBA Jun 29 '09 at 12:41
  • 21
    Nope! 4pm is 16:00:00 :) 4am is 04:00:00. – Jeremy Smyth Jun 29 '09 at 12:42
  • You column is of the right type, right? – Sampson Jun 29 '09 at 12:45
  • Thanks my clock was 12 hour clock so i was getting that resultset. :P Thanks again – MySQL DBA Jun 29 '09 at 12:51

4 Answers4

81

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.

Ajitesh
  • 17
  • 5
Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
18

You can use the following SQL to compare both date and time -

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-

enter image description here

It should work perfect.

Anjuman
  • 1,424
  • 15
  • 10
7

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.

Make sure your mydate column is a datetime type.

Sampson
  • 265,109
  • 74
  • 539
  • 565
4

Do you want to order it?

Select * From temp where mydate > '2009-06-29 04:00:44' ORDER BY mydate;
Sibu
  • 4,609
  • 2
  • 26
  • 38
Erik
  • 559
  • 1
  • 8
  • 17