Resolved Delete HOUR or DAY ?

[Feckie]

I need to delete all where timestamp is over 35hrs old, as the links are then void..
I have tried
$query = "DELETE FROM import WHERE Timestamp < NOW() - INTERVAL 35 HOUR";
But it seems to delete all over 24hrs old

When I use
$query = "DELETE FROM import WHERE Timestamp < NOW() - INTERVAL 3 DAY";
The older links are dead.

Any ideas

[MarkR]

Off the top of my head, date_add any good?

WHERE DATE_ADD(Timestamp, INTERVAL 35 HOUR) < NOW()

[Feckie]

Originally Posted by MarkR

Off the top of my head, date_add any good?

WHERE DATE_ADD(Timestamp, INTERVAL 35 HOUR) < NOW()

That Adds or subtracts a specified time interval from a date, what i want to do,
is Delete from database files older than 35 hours

[Keleth]

Have you tried doing a SELECT * FROM import where Timestamp < NOW() - INTERVAL 35 HOUR? It looks like it might be a timezone issue. Also try just a SELECT NOW() - INTERVAL 35 HOUR, and make sure it looks good.

[Old Pedant]

Mark's answer is correct.

He was only showing the WHERE part of the query.

The full query would be

DELETE FROM import WHERE DATE_ADD(Timestamp, INTERVAL 35 HOUR) < NOW()

But Keleth's answer should have worked, too.

Part of your problem may be that timestamp is a reserved word in MySQL. You really should try to avoid using reserved words as field and table names.

If that is the problem, though, you can just enclosed the name in BACKTICKS -- the ` character that shares a key with the ~ tilde.

DELETE FROM import WHERE DATE_ADD(`Timestamp`, INTERVAL 35 HOUR) < NOW()

or

DELETE FROM import WHERE `timestamp` < NOW() - INTERVAL 35 HOUR

[Feckie]

Hi Old Pedant,

I have just to admit what a plonker I am..

mindset 1 day = 12 hrs Must be getting older than I thought

now set to 3 days = 72hrs so set to delete to 71hrs and it's working

Go have a good laugh... :-)

[guelphdad]

Can you show us some sample rows from your table and which ones are being deleted that shouldn't be? Something doesn't seem right and it isn't the code above.

the DATE_ADD as written takes all dates and adds 35 hours to them. If the dates are within 35 hours of now(), adding the 35 hours will bring them to a value greater than now() and thus would fail the WHERE clause and not be deleted.

[Feckie]

Originally Posted by guelphdad

Can you show us some sample rows from your table and which ones are being deleted that shouldn't be? Something doesn't seem right and it isn't the code above.

the DATE_ADD as written takes all dates and adds 35 hours to them. If the dates are within 35 hours of now(), adding the 35 hours will bring them to a value greater than now() and thus would fail the WHERE clause and not be deleted.

ha ha read above