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!

For future sanity's sake, you may wish to modify your table to record the twitter_date column as an actual timestamp column. — Clockwork-Muse
– Clockwork-Muse, Commented Jul 25, 2011 at 17:06

Adriano Carneiro · Accepted Answer · 2011-07-25 15:47:06Z

8

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'

edited Jul 25, 2011 at 15:47

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

answered Jul 25, 2011 at 15:36

Noah

1,9661 gold badge14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriano Carneiro · Accepted Answer · 2011-07-25 15:45:59Z

2

You are comparing "6 Jul 2011" with 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'

edited Jul 25, 2011 at 15:45

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

answered Jul 25, 2011 at 15:38

sap

1,1416 gold badges41 silver badges63 bronze badges

Comments

Jim · Accepted Answer · 2011-07-25 15:34:14Z

0

You may want to look into MySQLs STR_TO_DATE function. This will give you a date and between should work as expected.

answered Jul 25, 2011 at 15:34

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Comments

Marc B · Accepted Answer · 2011-07-25 15:35:51Z

0

The query will fail. You're comparing two STRINGS, not dates. You need to tell MySQL explicitly that you want to treat the strings as dates. Since it's date information, store it in a date or datetime field type in MySQL, which saves you all this trouble:

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

Forcing your left-hand-side to be a proper date value will hint to MySQL that it should treat the right-hand-side as a date as well.

answered Jul 25, 2011 at 15:35

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:34:43Z

0

Once you have altered your table to hold the date data in proper DATETIME fields, you may want to refer to my answer to the following question regarding dates and filtering them, which may prove useful.

For example, in your query / data above, what would happen if you had no data for one of the dates you were querying? You'd get no output for that date, which may look odd when charting, etc. If you use the technique I describe in the attached question, you can get that additional information, and other useful stuff like the day of the week, month name, etc.

Select all months within given date span, including the ones with 0 values

Hope that helps!

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Jul 25, 2011 at 15:48

Dave Rix

1,6891 gold badge11 silver badges17 bronze badges

Collectives™ on Stack Overflow

Getting data between two date strings in MySQL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related