Prune by Date

[LPH]

Hi,

I've read the other posts but am either not understanding or looking how to prune by date. I want all posts prior to a certain date removed. What would be the mysql statement?

Thank you.

 

[Chris D]

The query would be the following:

DELETE FROM `xf_post` WHERE `post_date` =< 'UNIX_TIMESTAMP'

Where UNIX_TIMESTAMP is the Linux/Unix/Epoch time you want to delete from.

You can work this out by using an online converter: http://www.unixtimestamp.com/index.php

To delete all posts created on or before 20th August 2012 at 00:00, the exact query would be:

DELETE FROM `xf_post` WHERE `post_date` =< '1345438800'

 

[Chris D]

Just thought I'd add.

You could also consider archiving, rather than deleting.

This would involve moving all of the threads created before a certain date to a private forum. If you'd prefer that query instead no problem.

 

[LPH]

Thank you for the SQL statement. I keep getting an error 1064.

I'm deleting because the database is way too large and the information is way outdated.

 

[Chris D]

My bad. Try:

DELETE FROM `xf_post` WHERE `post_date` <= 'UNIX_TIMESTAMP'

 

[LPH]

Thank you. I was playing around with it and figured it out. Unfortunately I ended up putting in the wrong timestamp. LOL. I wiped out the posts. Anyway, a restore is on the way and I'll try again.

 

[ragtek]

This query doesn't delete all the other data (e.g. likes,attachments and it doesn't upgrade the thread & user posts counts

 

[LPH]

This query doesn't delete all the other data (e.g. likes,attachments and it doesn't upgrade the thread & user posts counts

Yep. I'm figuring that out right now. Any suggestions on how to get those updated ?

 

[ragtek]

it's not possible with only an query
you'll need to do this with an addon using the datamanager because it affects really many tables and cached values

 

[LPH]

Which add-on is available for pruning threads / posts so that all the tables are updated?

 

[Chris D]

You could try this one but it's paid: http://xenforo.com/community/resources/sonnb-messages-mass-actions.824/

 

[LPH]

it's not possible with only an query
you'll need to do this with an addon using the datamanager because it affects really many tables and cached values

Is there a list of tables involved with xf_posts?

Once the tables are truncated to the same date, then I should be able to rebuild the cache files in the admin, right?

For example, not only does the xf_post need to be truncated, the xf_threads, too

DELETE FROM `xf_thread` WHERE `post_date` <= '1262307661'