Getting data between two date strings in MySQL

Question

I have been recording Twitter data for a project I'm working on the date information is saved as Thu, 14 Jul 2011 06:21:48 +0000 in a string field.

How do I select data that falls between two dated using mySQL? I can get data larger than a value or smaller than a value but not between to values.

The data for example is:

Thu, 14 Jul 2011 06:21:48 +0000
Thu, 14 Jul 2011 12:18:21 +0000
Thu, 14 Jul 2011 18:48:00 +0000
Thu, 14 Jul 2011 23:48:02 +0000
Fri, 15 Jul 2011 06:48:10 +0000
Fri, 15 Jul 2011 12:48:00 +0000
Fri, 15 Jul 2011 18:43:32 +0000
Fri, 15 Jul 2011 23:44:08 +0000
Sat, 16 Jul 2011 06:47:08 +0000
Sat, 16 Jul 2011 12:46:49 +0000
Sat, 16 Jul 2011 18:45:41 +0000
Sat, 16 Jul 2011 23:41:27 +0000

My SQL string is:

SELECT * 
FROM twitter 
WHERE SUBSTR(twitter_date, 6, 11) >= '2011-06-15' 
AND SUBSTR(twitter_date, 6, 11) <= '2011-06-21'

I've tried BETWEEN statements as well but no luck.

Any help will be appreciated!

Answer 1

You can't use between because they are strings and not actual date fields. If you know the format of the date will always be the same, you can do the following: STR_TO_DATE(str,format)

SELECT * 
FROM twitter 
WHERE STR_TO_DATE(twitter_date, '%a, %c %b %Y %k:%i:%s') 
      between '2011-06-15' AND '2011-06-21'