CODEWITHSUNDEEP
mysql
John Smith
John Smith

Reputation: 487

How to increment the interval date on Mysql

Is it possible to increment the date value on mysql? say I have this:

+------+---------------------+
| PID  | mydate              |
+------+---------------------+
|    1 | 2016-08-06 08:00:00 |
|    2 | 2016-08-06 08:00:00 |
|    3 | 2016-08-06 08:00:00 |
+------+---------------------+

And I wanted to make each value incremented by 10 hours, something like this:

+------+---------------------+
| PID  | mydate              |
+------+---------------------+
|    1 | 2016-08-06 18:00:00 |
|    2 | 2016-08-07 04:00:00 |
|    3 | 2016-08-07 14:00:00 |
+------+---------------------+

I have tried this

update mytable set mydate = now() + interval 10 hour;

but that will update every value to 10 hours from now.

Upvotes: 1

Views: 681

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use variables for this:

update mytable cross join
       (select @i := 0) params
    set mydate = mydate + interval 10 * (@i := @i + 1) hour;

EDIT:

I notice there is an ordering in the original data. For that to work:

set @i = 0;

update mytable 
    set mydate = mydate + interval 10 * (@i := @i + 1) hour
    order by id;

Or even:

update mytable 
    set mydate = mydate + interval 10 * (id - 1) hour
    order by id;

This only works if id increments by 1 and has no gaps.

Upvotes: 3

Related Questions