MySQL Query to select data from last week?

Question

Hi I have a table with a date field and some other information. I want to select all entries from the past week, (week start from Sunday).

table values:

id        date

2   2011-05-14 09:17:25

5   2011-05-16 09:17:25

6   2011-05-17 09:17:25

8   2011-05-20 09:17:25

15  2011-05-22 09:17:25

I want to select all ids from last week, expected output is 5, 6, 8. (id 2 not in last week, and id 15 is in current week.)

How to write and SQL Query for the same.

Answer 1

Try this code:

SELECT id FROM tbl

WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY

AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY