CODEWITHSUNDEEP
phpmysqlkohana-3
alex
alex

Reputation: 490617

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

Upvotes: 2

Views: 886

Answers (4)

wallyk
wallyk

Reputation: 57794

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332731

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.

Upvotes: 1

Ross Snyder
Ross Snyder

Reputation: 1975

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

Upvotes: 2

Mathieu
Mathieu

Reputation: 5695

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')

Upvotes: 0

Related Questions