SQL query to update post_date (one post = one single date & time)

by

I have 120 posts of one category ready for my wordpress blog.
For the moment, posts are drafts and I don’t want to schedule them manually.

I want to publish them like this:

  • 4 articles each day from the 1st of August 2013
  • 1 at 10am / 1 at 2pm / 1 at 5pm / 1 at 8:30pm

What I need is the SQL query to:

  • Update date_post for the 120 posts, based on the needs described above
  • Change “draft” mode to “pending”
  • The publication order is based on the creation date of the posts: the older will be the first published.

I’m lost in the queries and loops.
If someone could help, it would be very very appreciated.

Thanks a lot,

Chris

1 Answer
1

I would not do this with SQL. A relatively simple PHP loop should work.

function bulk_schedule_posts_wpse_105834() {
  $args = (
    array(
      'cat' => 1,
      'posts_per_page' => -1,
      'post_status' => 'draft',
      'post_type' => 'post',
    )
  );
  $posts = new WP_Query($args);
  if (!$posts->have_posts()) return false;

  $date="2013-08-01";
  $times = array(
    '10:00',
    '14:00',
    '17:00',
    '20:30'
  );

  while ($posts->have_posts()) {

    global $post;
    $posts->the_post();

    if (0 !== $posts->current_post && 0 === $posts->current_post%4) {
      $date = date('Y-m-d',strtotime('+1 day',strtotime($date)));
    }

    $thisdate = $date.' '.$times[$posts->current_post%4].':00';
    $date_gmt = get_gmt_from_date($thisdate);
    $post->edit_date = true;
    $post->post_date = $thisdate;
    $post->post_date_gmt = $date_gmt;
    $post->post_status="future";

    wp_update_post($post);
  }
}
bulk_schedule_posts_wpse_105834();

Run that function once and then remove it.

What you are doing is moderately dangerous. Test that thoroughly on dummy data before using it on a live site. I don’t think it should actually destroy much data but it could make a big mess.

Barely tested. Possibly buggy. Caveat emptor. No refunds.

Leave a Comment