I wrote this script after I built my first WordPress datafeed site. After I loaded up the site, I realized that I had 5,000+ posts all made on the same day! Rather than repost everything, I wrote this quick script. Hopefully someone may find it useful.
What it Does
This script will connect to your WordPress database and change the post dates on all of your posts to random dates. You can define the range of dates by setting the two variables: $min_days_old, and $max_days_old. For example: if you set $min_days_old = 1 and $max_days_old = 30, the script will go through and change all of the post dates to be within the past 30 days.
How to Use It
Edit the MySQL connection info at the top. (Also be sure to change $wp_table to the name of your WordPress posts table if it is different.) Next, set the min and max days old. Now all you need to do is visit the script URL in your browser.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <?php // define mysql connect $dbname = "name"; $dbuser = "user"; $dbpass = "pass"; $dbhost = "localhost"; // this can usually stay 'localhost' $wp_table = "wp_posts"; // define wordpress table name $gmt_offset = '-8'; // -8 for California, -5 New York, +8 Hong Kong, etc. $min_days_old = 1; // the minimum number of days old $max_days_old = 90; // the maximum number of days old // connect to db mysql_connect($dbhost,$dbuser,$dbpass); mysql_select_db($dbname); $result = mysql_query("SELECT ID FROM $wp_table WHERE post_type = 'post'") or die(mysql_error()); while ($l = mysql_fetch_array($result)) { $post_id = $l['ID']; echo "Updating: $post_id <br>"; $day = rand($min_days_old, $max_days_old); $hour = rand(0, 23); $new_date = date( 'Y-m-d H:i:s', strtotime("-$day day -$hour hour") ); $gmt_new_date = date( 'Y-m-d H:i:s', strtotime("-$day day -$hour hour -$gmt_offset hour") ); mysql_query("UPDATE $wp_table SET post_date='$new_date', post_date_gmt='$gmt_new_date', post_modified='$new_date', post_modified_gmt='$gmt_new_date' WHERE ID='$post_id'") or die(mysql_error()); } echo "<hr>DONE!"; ?> |
Enjoy!
worked great thanks a lot!!!
You’re welcome. I’m glad someone got some use out of it!
Thanks for reading.
This is brilliant. I had a couple of old blogs that I wrote a lot of random junk on back in 2008. I hadn’t touched much since. I just ran this script and now it looks like they’re current.
Nice work on the coding.
Cool! Thanks for reading.
Hi…
Thanks for that, saved me really some time. One thing could be interesting, to make a JOIN in the SQL statement, so you can call for changes on only one category.
Thanks
Good idea, it would make it handier for sure. If you ever code that up I’d be happy to post it.
Thanks for reading!
holy shit, this is was the perfect workaround for a few sites im developing. thanks man!
Cool glad it worked for you. Thanks for reading!
WOW! Thanks for this awesome script. Worked like magic on a 50,000 page site! 🙂
50K is a lot! Thanks for reading!
Perfect!! I imported just over 28,000 records into WordPress and the plan was to write my own function next weekend for the dates, but… yours worked fabulously. Took a little over a minute but gave me 6 months of posts and couldn’t have been easier. Thanks for sharing!
You’re welcome. Thanks for reading & commenting!
Dude …..
you are Awesome ….
you saved my 8 months of work….
🙂 and a hug to you
Great work and keep it up
Cheers
Cool, glad you liked it. Thanks for reading & commenting!
Hey – pretty much what i was looking for. What extra code would be needed to restrict it to a specific category?
Thanks!!
Off the top of my head, I’m not sure. If I remember correctly categories are set according to their own id’s. The posts, in turn, reference these id(s). Have a look at your WP db using phpMyAdmin and you’ll see what I mean. The mod wouldn’t be hard, it would just be a matter of modifying the SQL query.
Thanks for reading & commenting!
Nice piece of code! Perhaps you can make a variant for updating the post_modified and post_modified_gmt date so that you tell the world that you update those posts. A random of 50 posts per day will be fine I think.
If I understand you right, you don’t want to modify the post date, but ONLY the post_modified date? You can do that easily by remove the post_date= and post_date_gmt= portions of the SQL query on line 31.
Thanks for reading!
this is just perfect, only one question tho, is it possible to run it for a range of posts and not all the posts?
Hi, yes you can do that pretty easily. The trick is to modify they SQL query on line 20. Right now the query looks like this:
SELECT ID FROM wp_posts WHERE post_type = ‘post’
Supposed I want to only update posts that are between Jan 1 and Apr 1 of 2011. I could change the query to look like this:
SELECT ID FROM wp_posts WHERE post_type = ‘post’ AND post_date > ‘2011-01-01 00:00:00’ AND post_date < '2011-04-01 00:00:00' Note, the date is international format. Meaning: Year-Month-Day. The time is in 24-hour format.
I really appreciate it, thanks so much.
Cheers
Alex
Is it possible to do this for comments instead of posts?
Yeah sure, I don’t see why not.
Awesome dude…Thanks a lot…
Worked awesomely. Thanks for the code. 😀
You’re welcome thanks for letting me know you found it useful!
Wow..what a script – it took me several days to find this but saved me hours and hours of work – thanks so much
I have it working nicely on a site with 1000’s of posts..that now look nice and new!
Any suggestions on how to feed it a set of post ID’s to just change posts with those ID’s
I did give it a whirl but my programming sucks
Many Thanks
Glad you liked it. You can do what you’re asking my modifying the SELECT query from this:
“SELECT ID FROM $wp_table WHERE post_type = ‘post'”
to this:
“SELECT ID FROM $wp_table WHERE post_type = ‘post’ AND (ID=1 OR ID=5 OR ID=33)”
This would only modify ID 1,5, and 33. Google “PHP MySQL tutorial” for more info on how this works.
Thanks for this wonderful script. Worked Perfectly…
when i changed $min_days_old = to – 40 (minus 40) some post date are changed for future Scheduled date but they are not Scheduled !
Is there any way to make the post status Scheduled instead of posted even the date is not yet come !
Hi glad you found it useful.
Yes there’s probably a way to do what you’re asking. Have a look at the database structure itself. Compare a post that has been posted already and one that is scheduled. There is likely some kind of flag that identifies if a post is scheduled or not. You just have to mimic that. Unfortunately I don’t really have time to work on that for free 😉 But I’m sure you can figure it out.
working fine.
thank you so much.
Great this worked a treat 😀 Perfect for dummy content and dev!
Thanks for reading and commenting!
This is exactly what I needed. I had to change the table name too but it worked like a charm. Thanks!
Great, glad you found it useful. Thanks for taking the time to comment!
Does it still working today?
Yup
ctrtard’s latest blog post: How to Install the Maxmind GeoIP2 Database and PHP API
Hi,
Exactly what I was looking for, why not to make it a plugin?
Absolutely great script and has solved this issue.
Such a simple script but so effective.
I salute you Sir – its people like you that make the wordpress dev community excellent.
Thankyou for your efforts on this
Glad you liked it
ctrtard’s latest blog post: How to Install the Maxmind GeoIP2 Database and PHP API