• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Prune by Date

LPH

Well-known member
#1
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
#2
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
#3
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
#4
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
#6
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
#7
This query doesn't delete all the other data (e.g. likes,attachments and it doesn't upgrade the thread & user posts counts
 
R

ragtek

Guest
#9
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
#12
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'