CODEWITHSUNDEEP
mysqlsqldatetime
Lazik
Lazik

Reputation: 2520

MySQL select * from last 5 days from a datetime field

I have a table which looks like this

id  datetime  tick_info(string)
0  16-10-2013 3:33:01  "33300"
1  17-10-2013 5:04:01  "003023"
2  17-10-2013 6:12:04  "3244"
3  19-10-2013 5:32:12  "3333332"
4  20-10-2013 8:14:44  "33321"
5  20-10-2013 9:12:11  "5821"
6  22-10-2013 10:32:11 "33111"

And so the data can span for 20 days and I want to select all rows with the last 5 days. Note that the last 5 days mean : the 5 latest distinct date in the table.

Ex : 17-19-20-22-23 in the case where there is no tick_info for the 21 and the 18.

The number of tick_info is variable so there might be 100 tick_info for the 20 and 5 for the 23.

I'm using mysql.

Update Should have mentioned I'm using mysql 5.5.32

Upvotes: 0

Views: 2798

Answers (3)

Rravindra Naik
Rravindra Naik

Reputation: 21

Please check my query it is cent percent working

I tested it

SELECT * FROM yourTable WHERE tick_info>=DATE_SUB(Now(), INTERVAL 5 DAY) group by tick_info order by tick_info DESC

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

Select 
* 
from 
'myTable' 
where 'datetime' >=
      (
           select  
          `datetime` 
          from myTable 
          group by DATE(`datetime`) 
          order by `datetime` desc 4,1
        )

Upvotes: 1

Antoniossss
Antoniossss

Reputation: 32517

SELECT * FROM yourTable WHERE tick_info>=DATE_SUB(tick_info, INTERVAL 5 DAY) group by tick_info order by tick_info DESC

Upvotes: 1

Related Questions