Select records where datetime is greater than the specified date

Question

Problem

I am trying to fetch all the records from table where date_time field is greater than 'Thu, 11 Jul 2013' by running the below mentioned query. Value in the date_time field is stored in this format => Thu, 11 Jul 2013 08:29:37. Any help will be great.

Datatype of field date_time is varchar

Query

SELECT * FROM table_name 
WHERE username = '[email protected]' 
AND STR_TO_DATE(date_time, '%a, %e %b %Y %H:%i:%s') >= 'Thu, 11 Jul 2013 00:00:00';

Answer 1

Here is yet another great example of why you should implement date/time fields in MySQL using the date, datetime, or timestamp field types and let your application deal with how to format the date for output.

Right now you are going to need to do something like:

SELECT * FROM table_name 
WHERE username = '[email protected]' 
AND STR_TO_DATE(date_time, '%a, %e %b %Y %H:%i:%s') >= STR_TO_DATE('Thu, 11 Jul 2013 00:00:00', '%a, %e %b %Y %H:%i:%s');

This query will not be able to use any index you have on your date_time field, so the query will be very inefficient. It will need to perform a full table scan, converting the value of each row in order to make the comparison.

What you should be doing is:

SELECT * FROM table_name 
WHERE username = '[email protected]' 
AND date_time >= STR_TO_DATE('Thu, 11 Jul 2013 00:00:00', '%a, %e %b %Y %H:%i:%s');

Here if you have your field in the MySQL datetime format, you just need to convert the input to a this format for matching. Since your field data is already in this format, you will be able to utilize an index for the search.