Issue
I have tried this query but it didn’t work:
UPDATE phonebooks SET created_at = DATE_ADD(created_at, INTERVAL 10 second)
My current records are like this :
| ID | DATE |
|---|---|
| 1 | 2022-03-24 10:30:34 |
| 2 | 2022-03-24 10:30:34 |
| 3 | 2022-03-24 10:30:34 |
| 4 | 2022-03-24 10:30:34 |
| 5 | 2022-03-24 10:30:34 |
| 6 | 2022-03-24 10:30:34 |
| 7 | 2022-03-24 10:30:34 |
I want to get these records like this :
| ID | DATE |
|---|---|
| 1 | 2022-03-24 10:30:44 |
| 2 | 2022-03-24 10:30:54 |
| 3 | 2022-03-24 10:31:04 |
| 4 | 2022-03-24 10:31:14 |
| 5 | 2022-03-24 10:31:24 |
| 6 | 2022-03-24 10:31:34 |
| 7 | 2022-03-24 10:31:44 |
Solution
Right now you are just adding 10 seconds to each row, but you need something that would increase your interval depending of the row you are updating. Based on your example, you could use the ID as a multiplier.
UPDATE phonebooks SET created_at = DATE_ADD(created_at, INTERVAL 10 * id second)
This won’t work exactly as specified if you have gaps in your IDs.
Answered By – Hendrik
Answer Checked By – Senaida (BugsFixing Volunteer)