How to set random datetime in MySQL

Categorized as Databases

Imagine you have to mass insert or update the values in your created_at or updated_at fields. You can easily achieve that with the following SQL query:

-- for update
update `posts` set `updated_at` =
    FROM_UNIXTIME(
        UNIX_TIMESTAMP('2022-04-10 14:53:27') + FLOOR(0 + (RAND() * 86400))
    )

-- for insert
insert into `posts` values (
    FROM_UNIXTIME(
        UNIX_TIMESTAMP('2022-04-10 14:53:27') + FLOOR(0 + (RAND() * 86400))
    )
)

You can also choose the spread in seconds, 3600 is 1 hour, 86400 is 1 day, 31536000 is 1 year.

Leave a reply

Your email address will not be published.