Search code examples
phpmysqlwordpressbitnami

Move publish date of all wordpress posts back by one day

I have deployed a Bitnami AWS stack for Wordpress and the site seems to be functioning fine. Unfortunately, I miscalculated the publish date for around 40 posts. I need to update all these posts (either already published or scheduled) to move the publish date back by one day. All bulk edit plugins I could find only allow post dates to be set to a particular value, they don't seem to provide a way to alter the dates using a delta calculation (e.g. -1 day in my case).

Is there a way to update the dates either using the MySQL database or PHP? I do have SSH access to the wordpress deployment.

Solution

  • Make a backup first and execute this SQL query to update all posts' dates, I disabled strict mode because MySQL 5.7 doesn't accept zeros in dates anymore so it'll be ignored:

    SET sql_mode = '';

update wp_posts
set 
post_date = DATE_ADD(post_date, INTERVAL -1 DAY),
post_date_gmt = DATE_ADD(post_date_gmt , INTERVAL -1 DAY);

    For scheduled posts you can use wp_insert_post action but you should tweak it for new posts if not scheduled.