MySQL Query to select data from last week?

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.

Solution 1:

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

Solution 2:

SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

Solution 3:

SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)

I use the YEARWEEK function specifically to go back to the prior whole calendar week (as opposed to 7 days before today). YEARWEEK also allows a second argument that will set the start of the week or determine how the first/last week of the year are handled. YEARWEEK lets you to keep the number of weeks to go back/forward in a single variable, and will not include the same week number from prior/future years, and it's far shorter than most of the other answers on here.