[SOLVED] How can I update a time interval 10 seconds greater than the previous record in MySQL?

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)

Leave a Reply

Your email address will not be published.