Randomize the Dates of WordPress Posts

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!

Leave a comment

*

*

CommentLuv badge

40 Comments

  • dom

    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!

  • xgarrettx

    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!

  • Dorian

    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!

  • Mridul

    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!

  • Austin

    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!

  • henri

    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!

  • Alex

    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.

      • Alex

        I really appreciate it, thanks so much.

        Cheers
        Alex

  • KJ Ross

    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!

  • websam

    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.

  • lnwxxx

    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?

  • 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

 
 
 

Related posts by category

Related posts by tag