How do I bulk change the post_date?

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]

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.

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?

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?