Select data from last week mysql query

Question

I have a table with a datefield and I want to select all the entries that came from the last week,Saturday to Sunday(not last 7 days).

Table values

Date               ID    Value
8/3/2015 8:03 PM    a     10        
8/4/2015 8:03 PM    b     20        
8/5/2015 8:03 PM    c     30    
8/6/2015 8:03 PM    d     40    
8/7/2015 8:03 PM    e     50    
8/9/2015 8:03 PM    f     60        
8/10/2015 8:03 PM   g     70

I only want to select id from last which are a,b,c,d,e. How do i write the mysql query in this ?

dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html — JamieD77
@Bulat i have tried to used this SELECT *FROM StudentPurchasedHistoryTable WHERE [Purchased Date] >= DATEADD(day,-7, GETDATE()) but it only display all of them — RiaMamaMia

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-08-09T14:49:24

This is a bit tricky. One method is to try something like week(), but that has a problem at the beginning of the year.

Another approach is to get the date of midnight on Sunday and use that for the calculation. I think this expression does that:

 date_sub(curdate(), interval (weekday(curdate()) + 1) % 7) - 1 day)

You can then plug this into the where clause:

select v.*
from values v
where v.date < date_sub(curdate(), interval (weekday(curdate()) + 1) % 7) - 1 day) and
      v.date >= date_sub(curdate(), interval (weekday(curdate()) + 1) % 7) - 8 day);

Note: this formulation can make use of an index on date.

Select data from last week mysql query

1 Answers