WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] How do I bulk change the post_date? (5 posts)

  1. Supplybrary
    Member
    Posted 2 years ago #

    Hello,

    Godaddy Hosting
    PHP 5
    Wordpress 3.3.1

    I imported a thousand posts with a csv file but some (a bunch) came in with the same timestamps and is causing next post / previous post issues.

    Is there a way to change the post_date in phpMyAdmin, so that the post_date is

    2012-20-02 15:24:40
    2012-20-02 15:24:41
    2012-20-02 15:24:42
    2012-20-02 15:24:43
    2012-20-02 15:24:44

    etc...?
    ...or something similar, as long as they are different. Any hints or direction would be greatly appreciated.

  2. rockt204
    Member
    Posted 2 years ago #

    Use the following SQL code in phpMyAdmin.
    The first date under post_date is the current and the other is the new date.
    [sql]UPDATE wp_posts SET post_date = REPLACE (
    post_date,
    ’2008-01-30′,
    ’2009-01-30′)[/sql]
    [Facebook invite removed]

  3. Supplybrary
    Member
    Posted 2 years ago #

    Thank you for the lead. I was able to make that work for one entry by including the whole time stamp like this.

    [sql]UPDATE wp_posts SET post_date = REPLACE (post_date, '2011-05-28 02:11:16', '2012-02-26 07:02:56')[/sql]

    It was still one row at a time, so i kept looking and pieced together this.

    DECLARE @start datetime
    DECLARE @end datetime
    
    SELECT @start = '2012-02-18 01:47:50', @end = '2012-02-26 07:02:56'
    
    WHILE @start <= @end
    begin
    REPLACE wp_posts(post_date)
    VALUES(@start)
    SET @start = @start + 1
    END

    That doesn't work. Has an error.

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @start datetime
    DECLARE @end datetime

    SELECT @start = '2012-02-18 01' at line 1

    So i looked at the manual, and found interesting functions.

    DATE_ADD and TIMESTAMPADD. I'm still looking into it. Thanks again.

  4. Supplybrary
    Member
    Posted 2 years ago #

    I'm still not there. I did learn how to foobar all the posts. DO NOT TRY THIS AT HOME.

    UPDATE wp_posts
    SET post_date = '2012-02-18 01:47:50.000'

    That code sets all the dates the same and wipes out all page links. So don't try it.

    I'm looking at this currently.

    DECLARE @Date DATETIME
    SET @Date = '2012-02-18 01:47:50'
    UPDATE wp_posts SET post_date = ( @Date := @Date +1 )

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Date DATETIME
    SET @Date = '2012-02-18 01:47:50'
    UPDATE wp_posts SET p' at line 1

    Any clues?

  5. Supplybrary
    Member
    Posted 2 years ago #

    With some help from ssmusoke at StackOverflow and the MySQL manual. I have a SQL command that will re sequence all the post dates.

    [sql]UPDATE wp_posts SET post_date = DATE_ADD(post_date,INTERVAL id SECOND)[/sql]

    That takes the post ID and applies it to the seconds of the post date.

    From other comments, I may have to also update post_date_gmt and the modified date columns.

    Anyone know of issues with post_date and post_date_gmt not matching?

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags