It seems like this should be a simple exercise, but I'm stuck...
My database uses the MySQL timestamp NOW(). I'd like to delete all entries in the database that are older than a certain number of days. At first I tried DATEDIFF in the mysql_query, but later realized that DATEDIFF was added to version 4.1.1 -- my server is running an earlier version of MySQL.
I know how to do this sort of deletion using a unix timestamp, but I'm trying to make it work without going in that direction. Can anyone give me a hint? I'd really like to figure it out on my own, but I need a nudge in the right direction.
Thanks!
[SOLVED] Calculations using NOW()
Moderator: General Moderators
- charp
- Forum Commoner
- Posts: 85
- Joined: Sun Oct 26, 2003 3:00 pm
- Location: Rancho Cucamonga, Calif. USA
[SOLVED] Calculations using NOW()
Last edited by charp on Sun May 15, 2005 11:05 pm, edited 1 time in total.
have a look at DATE_SUB/DATE_ADD (http://dev.mysql.com/doc/mysql/en/date- ... tions.html)
(untested)
or more optimised
(untested)
Code: Select all
SELECT *
FROM table
WHERE DATE_ADD(datecolumn, INTERVAL 6 DAY) < NOW()Code: Select all
SELECT *
FROM table
WHERE datecolumn < DATE_SUB(NOW(), INTERVAL 6 DAY)