sql - 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

select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();