How to change date and time ascending in database MYSQL?

user_16232432 0
Viewed 40

I have over 100,000 entries with the same date and time. How to change to add 10 minutes and make the date also change as the next day?

I have:

2020-01-01 23:50:00
2020-01-01 23:50:00
2020-01-01 23:50:00
2020-01-01 23:50:00
2020-01-01 23:50:00

etc....

I want:

2020-01-01 23:50:00
2020-01-02 00:00:00
2020-01-02 00:10:00
2020-01-02 00:20:00
......
2020-01-31 00:50:00
2020-02-01 00:00:00
2020-02-01 00:10:00
2020-02-01 00:20:00

etc

mysql sql
1 Answers

One simple method uses variables in an update:

set @minutes = -10;

update t
    set col = col + interval (@minutes := @minutes + 10) minute
    where col = '2020-01-01 23:50:00';

If you just want a select, then use row_number():

select t.*,
       (col + interval 10 * row_number() over (order by col) - 10 minute)
from t;

Here is a db<>fiddle showing that both work.

user_11440350
Related