Search code examples
mysqlsqldatetimephpmyadminmysql-error-1064

How to resequence all dates in one column

Godaddy Hosting, PHP 5, MySQL 5.0, Wordpress 3.3.1

I set all my posts to the same publish date and it wiped out the next post / previous post links. There were thousands.

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.

I've tried this in MySQL / phpMyAdmin

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

but it errors out.

#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 hints or direction would be greatly appreciated.

Solution

  • I am assuming that the wp_posts table has a post_id field which is the unqiue identifier for each post (you may need to change the column there if its different) 

    UPDATE wp_posts SET post_date = DATE_ADD(post_date,INTERVAL post_id SECOND)

    The above query will add a number of seconds based on the post_id value which means that the latest posts will have later dates than the earlier posts. If you want the reverse then use DATE_SUB