Getting data between two date strings in MySQL

Question

I am writing Twitter data for a project that I am working on date information is saved as Thu, 14 Jul 2011 06:21:48 +0000 in the line field.

How to select data between two dated using mySQL? I can get data greater than a value or less than a value, but not between the values.

Data, for example:

 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:

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

I also tried the BETWEEN instructions, but no luck.

Any help would be appreciated!

Answer 1

You cannot use between them because they are strings and not valid date fields. If you know that the date format 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' 

Answer 2

You are comparing July 6, 2011 to 2011-06-15.

It should be

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

Answer 3

You might want to learn the MySQL function STR_TO_DATE . This will give you a date, and between them should work as expected.

Answer 4

The request will fail. You are comparing two STRINGS, not dates. You need to explicitly tell MySQL that you want to treat strings as dates. Since it contains a date, save it in the date or time field of the date in MySQL, which will save you all these problems:

 SELECT STR_TO_DATE(SUBSTR(twitter_date, 6, 11)) >= '2011-06-15' ... 

Forcing the left side to the correct date value will tell MySQL that it should treat the right side as a date.

Answer 5

After you have modified the table for storing date data in the corresponding DATETIME fields, you can refer to my answer to the following question about dates and their filtering, which may be useful.

For example, in your query / data above, what happens if you did not have data for one of the dates you requested? You will not have a way out for this date, which may seem strange when scheduling, etc. If you use the technique described in the attached question, you can get this additional information and other useful things, such as day of the week, month of name, etc.

Select all months within a given date range, including values ​​with 0 values

Hope this helps!