WordPress.org

Ready to get started?Download WordPress

Forums

Mysql all posts to set all posts as future date (5 posts)

  1. stevekellar
    Member
    Posted 2 years ago #

    If I have 100 posts and would like to change all their status to future date to be published in 2012 - what mysql code would I run to mass change all post dates and status?

    Need to change from published to future scheduled post date on all posts. I need all my 2010 articles to release to published on that date in 2012.

    Any help is appreciated,
    Steve

  2. David Gard
    Member
    Posted 2 years ago #

    Do you use PHPMyAdmin? If so, login to it, select the database that you want to amend and first make a backup copy (just in case!).

    Click 'SQL' to input a Query - you should now see a large white box. The code below should update the posts stats, the publish and publih (GMT) dates, and the modified and modified (GMT) dates of all posts that were from 2010. This will set them all to 1st January 2012.

    UPDATE {database_name}.wp_posts SET {database_name}.post_status = 'future' WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET {database_name}.post_date = '2012-01-01 06:00:00' WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET {database_name}.post_date_gmt = '2012-01-01 06:00:00' WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET {database_name}.post_modified = '2012-01-01 06:00:00' WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET {database_name}.post_modified_gmt = '2012-01-01 06:00:00' WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';

    This is untested, so there may be a couple of odd syntax errors in there, but PHPMyAdmin should tell you about them. And just to remind you, make a backup of your database first.....

  3. stevekellar
    Member
    Posted 2 years ago #

    Thankyou for such a quick response!

    To be clear before I apply that solution - it changes the year values to 2012...does it keep the month, day, time intact as original set/kept values?

    Steve

  4. David Gard
    Member
    Posted 2 years ago #

    No, it will blitz the date and set to 01/01/2012.

    YEAR({database_name}.post_date) = 2012 I think is what you are after, but never done it so I'm not sure - definatly, 100%, backup your database first, before you try the below.

    UPDATE {database_name}.wp_posts SET {database_name}.post_status = 'future' WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET YEAR({database_name}.post_date) = 2012 WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET YEAR({database_name}.post_date_gmt) = 2012 WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET YEAR({database_name}.post_modified) = 2012 WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
    
    UPDATE {database_name}.wp_posts SET YEAR({database_name}.post_modified_gmt) = 2012 WHERE YEAR({database_name}.post_date)=2010 AND {database_name}.post_type='post';
  5. stevekellar
    Member
    Posted 2 years ago #

    Thanks for your help.

Topic Closed

This topic has been closed to new replies.

About this Topic