[SOLVED] Calculations using NOW()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

[SOLVED] Calculations using NOW()

Post 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!
Last edited by charp on Sun May 15, 2005 11:05 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
User avatar
charp
Forum Commoner
Posts: 85
Joined: Sun Oct 26, 2003 3:00 pm
Location: Rancho Cucamonga, Calif. USA

Post 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.
Post Reply