0

I have a table consisting reports, I want to select data of last 7 day's. So in below image you can see 3 column day's month year.

What is the sql query to select last 7 day's.

enter image description here

Gaurav Gandhi
  • 3,041
  • 2
  • 27
  • 40
Ankit Pundir
  • 29
  • 6
  • Last 7 days with regard to _which_ date? Your question is unclear to me. – Tim Biegeleisen Feb 05 '16 at 04:21
  • Try this http://stackoverflow.com/questions/27599557/how-to-get-last-7-days-data-from-current-datetime-to-last-7-days-in-sql-server – Piyush Gupta Feb 05 '16 at 04:25
  • why store your dates like that and not simply a date type in one column? – Niagaradad Feb 05 '16 at 04:36
  • @ankit-pundir you should store date in single column – Meer Feb 05 '16 at 12:22
  • i want to store data in single column but due to performance i try store separately. i have possible 1 million rows in one month and grow up every month. so campare string is slower than compare numbers. that's why facebook store their user id in number format. – Ankit Pundir Feb 05 '16 at 18:04

2 Answers2

1

try something like this, 

    SELECT 
            *    
    FROM 
            < your_table > 
    WHERE 
            STR_TO_DATE(CONCAT(`day`, ',', `month`, ',' ,`year`), '%e,%m,%y') 
            BETWEEN 
            DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE();

If you want to use latest date from the same table,

try fetch max date separately with query , 

SELECT MAX(STR_TO_DATE(CONCAT(`day`, ',', `month`, ',' ,`year`), '%e,%m,%y')) AS maxdat FROM < your_table >

store it, and use it in the main query in-place of CURDATE(),

or,

you can use the same query as a sub-query if you don't have any performance issues.

Hytool
  • 1,358
  • 1
  • 7
  • 22
0

On which basis you want last 7 days entry.

Try this to find date before 7 days:

WHERE t.date >= DATE_ADD(CURDATE(), INTERVAL -7 DAY);

OR

WHERE t.date >= DATE(NOW()) - INTERVAL 7 DAY

Or you can go through Date_doc documentation.

ojus kulkarni
  • 1,877
  • 3
  • 25
  • 41