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

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.

Read More

Anyone know quick way to get this done using phpMyAdmin to edit the MySQL database or some other method?

Related posts

Leave a Reply

4 comments

  1. 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 ;

  2. 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)