Mysql Compare two datetime fields
Solution 1
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.
Solution 2
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-
It should work perfect.
Solution 3
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.
Solution 4
Do you want to order it?
Select * From temp where mydate > '2009-06-29 04:00:44' ORDER BY mydate;
Comments
-
MySQL DBA almost 2 years
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!
-
MySQL DBA about 15 yearsno i m getting all the result set for todays date not after the datetime i have specified for ex:- the time i have given is 4PM and i am getting 11AM records in that.
-
Jeremy Smyth about 15 yearsThe time you've given is 4am.
-
Erik about 15 yearsTry 'Select * From temp where mydate > '2009-06-29 16:00:44';'
-
Gergely Lukacsy about 9 yearsBrilliant. Mysql is comparing string instead of dates! I'll keep this in mind from now on. Great answer, upvoted!