Change the year of all posts in a particular category to 2012

Snippet! Change the year of all posts in a particular category to 2012 with a single SQL query (use with phpMyAdmin or the MySQL command line interface)

UPDATE wp_posts AS p
  JOIN wp_term_relationships AS tr ON tr.object_id = p.id
  JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
  JOIN wp_terms AS t ON tt.term_id = t.term_id
  SET p.post_date = REPLACE(p.post_date, YEAR(p.post_date), 2012)
  WHERE t.slug = 'my-category-slug' AND tt.taxonomy = 'category';

Obviously replace my-category-slug with the category you’d like to target and perform the same magic on the p.post_date_gmt field. Not entirely sure why you would use such a technique, but it was asked by Jim Edwards on Twitter.

About the author

Konstantin Kovshenin

WordPress Core Contributor, ex-Automattician, public speaker and consultant, enjoying life in Moscow. I blog about tech, WordPress and DevOps.

2 comments

  • Thanks again for this… It is an odd request, I admit. Here’s the back story. We created an online Bible reading schedule for our congregation at church located here http://visitgraceway.org/reading. Basically they are posts for each day of the year, that then are displayed in a category on the site & twittered out daily. So people can follow via he site, twitter, RSS or via email (feedburner).

    They came to us this week and said they would like to start it all over again this year, hence the need to change the publication date to 2012 instead of 2011.

    • Jim, you’re welcome! You might also want to change the status to scheduled to make sure the posts are published by the WordPress Cron. Have a great 2012!