Increment date/time value by second with MySQL query

3 Views

I’m trying to increment time in the given range of MySQL cells from the specific value + 1 second each time using the following query: 

set @mytime= -1;
update wp_posts set post_date = date('2015-04-03 03:00:07') + interval (@mytime := @mytime + 1) second WHERE `wp_posts`.`ID` > 229 AND `wp_posts`.`ID` < 999;

The problem is it sets correct time for the cell 229, but starting from cell 230 it begins from 00:00:00 and then adds 1 new second for every next cell, as expected.

Read More

So instead of 

2015-04-03 03:00:06
2015-04-03 03:00:07
2015-04-03 03:00:08
etc

I have 

2015-04-03 03:00:06
2015-04-03 00:00:00
2015-04-03 00:00:01
2015-04-03 00:00:02
and so on.

Where is the bug in the query?

Post Views: 3

Related posts

1 comment

  1. date('2015-04-03 03:00:07') just returns the date part, it discards the time of day, so it’s treated as being midnight when you add seconds to it. Change it to:

    update wp_posts set post_date = cast('2015-04-03 03:00:07' AS DATETIME) + interval (@mytime := @mytime + 1) second WHERE `wp_posts`.`ID` > 229 AND `wp_posts`.`ID` < 999;

    The reason cell 229 wasn’t affected by this is because your WHERE clause says ID > 229, not ID >= 229. So cell 229 isn’t being updated at all.

Comments are closed.