datetime - Select records from one week previous in mysql

Question

I have data that has a date (Y-m-d H:i:s) column (the type is datetime). I'd like to select all records from 1 week before a date I enter (in the below example: 2011-09-17 00:00:00, but am having some trouble. Here's what I've got:

SELECT * FROM emails WHERE (DATE(date) = date_sub(date('2011-09-17 00:00:00'), 1 week))

What am I doing wrong? Thanks

Answer 1

I think you're missing INTERVAL at the front of 1 week:

SELECT *
FROM emails
WHERE (DATE(date) = date_sub(date('2011-09-17 00:00:00'), INTERVAL 1 week));

Here is a query that I ran that does work for the DATE_SUB() part:

SELECT *
FROM wp_posts
WHERE post_modified > DATE_SUB(CURDATE(), INTERVAL 4 WEEK);

You can use a negative value to go do a "N weeks before given date" query so something like this would work:

SELECT *
FROM wp_posts
WHERE post_modified > DATE_SUB(CURDATE(), INTERVAL -1 WEEK);

Or:

SELECT *
FROM emails
WHERE (DATE(date) = date_sub(date('2011-09-17 00:00:00'), INTERVAL -1 week))