Prune by Date

LPH

Well-known member
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

XenForo developer
Staff member
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

XenForo developer
Staff member
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

Well-known member
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.
 

LPH

Well-known member
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.
 
R

ragtek

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

LPH

Well-known member
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 ?
 
R

ragtek

Guest
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

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

LPH

Well-known member
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'
 
Top