How can I set a random timestamp for a row based on a columns value in mysql?

I have an app that I built and found an issue with it.

Essentially there are created_at and ended_at timestamp columns.

The ended_at function in my app had a bug so now I have a few hundred thousand rows that have no ended at timestamp.

I want to go through each row and based on the created_at column, generate an ended_at timestamp that’s anywhere from 5 seconds to 8 hours away.

This is what I came up with, I just want to make sure it’s what I want before I run it on 500k rows.

UPDATE streams SET ended_at = FROM_UNIXTIME(
        UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800))
    )
    WHERE ended_at IS NULL LIMIT 1;

I have tested it and it appears to function correctly and i have backups of my database, but I just want to be 100% sure.

Thanks

I like to check out a computed column as part of a read only select first just to make sure it shows the values I’d expect.

SELECT *
    ,UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800)) as ended_at_new
        from streams
            WHERE ended_at IS NULL

If you see the correct variation and values you were expecting to see then your update query will work fine.

For extra protection you could run the update in a transaction:

begin tran;

UPDATE streams SET ended_at = FROM_UNIXTIME(
        UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800))
    )
    WHERE ended_at IS NULL

select count(ended_at) where ended_at IS NULL #should see 0

select * from streams limit 0, 1000; #add better filters to correctly identify records you want to check

rollback tran;

If you are happy with what you saw, change the rollback to commit and run it again.