Page 1 of 1

[SOLVED] Calculations using NOW()

Posted: Sun May 15, 2005 3:21 pm
by charp
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!

Posted: Sun May 15, 2005 5:01 pm
by timvw
have a look at DATE_SUB/DATE_ADD (http://dev.mysql.com/doc/mysql/en/date- ... tions.html)

(untested)

Code: Select all

SELECT *
FROM table
WHERE DATE_ADD(datecolumn, INTERVAL 6 DAY) < NOW()
or more optimised

Code: Select all

SELECT *
FROM table
WHERE datecolumn < DATE_SUB(NOW(), INTERVAL 6 DAY)

Posted: Sun May 15, 2005 11:05 pm
by charp
Timvw,

I looked right at those functions, but couldn't wrap my mind around how to use them. Now that I look at your code... Duh!

Thank you for the helping hand.