Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find results in certain date range

I am trying to only grab records that fall in a certain date range. The problem is that the timestamp and the date are stored as a string in the same cell. I want to only grab rows with a date that falls betweed 2013-05-01 and 2013-05-03.

date (stored as string)

2013-05-01T23:19:44
2013-05-02T23:19:40
2013-05-06T23:19:46
2013-05-06T23:15:17

mysql

SELECT * FROM table WHERE date BETWEEN 2013-05-01 AND 2013-05-03
like image 567
user2359318 Avatar asked May 07 '13 17:05

user2359318


People also ask

How can you limit your search results to a particular range of dates?

Using a Time Frame Sometimes you may want to only find information published during a specific time frame. To limit Google results by date, click on Tools, then click on Any Time and choose the filter or Custom Date Range that interests you.

How do I filter Google search results?

Below the search box, click Tools. To add filters: In the Tools bar, click the filter and select an option. To remove filters: In the Tools bar, click Clear.


1 Answers

Try

SELECT *
  FROM table1
WHERE STR_TO_DATE(`date`,'%Y-%m-%d') BETWEEN '2013-05-01' AND '2013-05-03'

SQLFiddle

As @FreshPrinceOfSO absolutely correctly noted no index will be used in that case

like image 165
peterm Avatar answered Sep 21 '22 20:09

peterm