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

Prune by Date

Discussion in 'XenForo Questions and Support' started by LPH, Aug 19, 2012.

  1. LPH

    LPH Well-Known Member


    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.
  2. Chris D

    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'
  3. Chris D

    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.
  4. LPH

    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.
  5. Chris D

    Chris D XenForo Developer Staff Member

    My bad. Try:

    DELETE FROM `xf_post` WHERE `post_date` <= 'UNIX_TIMESTAMP'
  6. LPH

    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.
  7. ragtek

    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[​IMG]
  8. LPH

    LPH Well-Known Member

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

    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
  10. LPH

    LPH Well-Known Member

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

    Chris D XenForo Developer Staff Member

  12. LPH

    LPH Well-Known Member

    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'

Share This Page