I had the expiration date set at 90 days. I changed it to 60 days, and yet all the classifieds are still there despite some being over 60 days old. Only new ads receive the 60 day expiration. How can I remove/purge all ads over 60 days old? Is there an SQL one-liner that will do the trick?

Comments

mcurry’s picture

mcurry commented
Title: Changing expiration date » Changing default expiration date doesn't affect existing ads' expiration
Status: Active » Closed (works as designed)

This is by design. As you know, the settings control the expiration date set at ad creation (or renewal) and do not alter existing ads. (If your site charges a fee for ads, there'd be a bunch of unhappy customers.)

In order to fix this, you need to deduct time from the expires_on column in the edi_classified_nodes table. You can do this by executing an SQL query to reduce the existing, non-expired ads' expiration date. Please back up your database before you try this -- use direct SQL queries at your own risk, YMMV, it will void your warranty, I'm not responsible if it breaks your site, etc.. I haven't tested the query presented here, so if you aren't really good at SQL, you should not do this.

The expiration timestamps are stored as unix timestamps (# of seconds since 1970). First, calculate the # of seconds to be removed. There are 84,600 seconds in every day.

You want to reduce the expiration dates by 30 days. 30 * 84,600 is 2,592,000 seconds. (That many? Wow!)

So, the update query you want is something like this (MySQL version) :

update edi_classified_nodes set expires_on = (expires_on - 2592000);

(Note: I've not tested this, so it may be syntactically incorrect but you get the idea.)

After executing the query, you should execute cron.php, so that the ed_classified module can age (and purge) old ads.

Check your watchdog log. Don't be surprised if you see a bunch of newly expired ads.

If this breaks things or otherwise messes up your site, restore your backup. You did make a database backup, didn't you?