Help with writing a MySQL query involving date differences.

Question

I need to delete rows where a datetime field is over 2 weeks old.

This is what I have came up with

$duration = Date::WEEK * 2; // int(1209600)
$query = 'DELETE FROM properties
            WHERE TIMEDIFF(' . date(DATE_ISO8601) . ', reserved_datetime) > ' . $duration;

I don't often write complicated queries (preferring to do stuff in PHP, where I'm more comfortable) but I'd like to know more about them, plus doing this sort of thing in PHP would be very inefficient and I am handling a large amount of rows.

Anyone know what I'm doing wrong? Cheers.

Update

I gave Wallyk's answer a shot, changing it slightly in phpMyAdmin to SELECT just so I could see what was going on.

This is what I used

SELECT *
FROM properties
WHERE date_sub( `reserved_datetime` , INTERVAL 2 week ) >0
LIMIT 0 , 30

The only problem however, is that it has returned rows where the reserved_datetime is 2010-02-28 10:45:59, definitely less than 2 weeks ago (from now).

I thought of checking MySQL's internal date. I have been using date(DATE_ISO8601) in my queries, because MySQL's NOW() wasn't exactly right (it just returned if interested 2010-02-28 20:09:19).

Is there a way to specify the current date in that query? Any other suggestions?

Many thanks

Another Update

Here is a screenshot from phpMyAdmin that may demonstrate anything better than my words can. Oh, and the reason it has returned 3 only is because all the others have blank values, i.e. 0000-00-00 00:00:00

Answer 1

I don't have a mysql database so I can't say if it works for sure, but it does in postgresql:

DELETE FROM properties WHERE (NOW() - reserved_datetime < interval '2 weeks')

Answer 2

Try this instead:

$query = 'DELETE FROM properties
         WHERE date_sub(reserved_datetime, interval 2 week) > 0';

This assumes that reserved_datetime is the field name in the table.

(Tested with MySQL 5.0.46-standard.)

Answer 3

wallyk's answer is not correct. Think about what you're doing - subtracting two weeks from almost any date will still be greater than zero (zero = 1/1/1970). I think you want something more like this:

DELETE FROM properties WHERE DATE_SUB(NOW(), INTERVAL 2 WEEK) > reserved_datetime

Answer 4

Use:

 FROM PROPERTIES p
WHERE p.reserved_datetime <= DATE_SUB(NOW(), INTERVAL 2 WEEK)

Mind that because of using NOW(), the two week old date will include the time portion.