How to offset dates in a MySQL database by 1 second?

Question

I've imported a bunch of posts into a Wordpress site that all have the exact same date. I'd like to edit the post dates so they are offset by one second. My concern is that there may be a performance issue when all of the dates are the same. Anyone know quick way to get this done using phpMyAdmin to edit the MySQL database or some other method? Thanks in advance.

Answer 1

UPDATE table SET mydate = DATE_ADD(my_date, INTERVAL 1 SECOND);

Answer 2

You could set them all to be 'now' + id.

It might look like;

 UPDATE  wp_posts
 SET     createdDate = DATE_ADD(now(), INTERVAL wp_posts.wp_id SECOND);

Answer 3

SET @r := '2009-04-14';
UPDATE  mytable
SET     mydate = (@r := @r + INTERVAL 1 SECOND);

Or in a single query, if your cannot keep the session state:

UPDATE  mytable,
        (
        SELECT  @r := '2009-04-14'
        ) q
SET     mydate  = (@r := @r + INTERVAL 1 SECOND)

Answer 4

Before you mess with this, I suggest that you make sure that in fact have a problem with simultaneous times.

I quite often find that messing with the data like this has unintended consequences. And I'd be moderately surprised if the problem really is significant.

It appears to me that I'm seeing proposals that will set all the rows to the same offset value.

Assuming you have an integer surrogate key, and the rows are adjacent, you could use

UPDATE table
SET mydate = DATE_ADD(my_date, INTERVAL id - SECOND)
WHERE id BETWEEN AND ;