Search code examples
phpmysqlkohana-3

How to delete rows based on date differences with MySQL?

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

query

Solution

  • 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.