MySQL: Update timestamp field with random dates

Today had to add a random unixtime date to a customer's table. Fortunately, MySql provides the RAND() function as well with native support to unixtime (EPOCH), which makes this a breeze.

The formula we need to use is Rand() * (end - start) + start. To obtain the proper values, you use the sample below.

SELECT UNIX_TIMESTAMP('2014-01-01') AS start, UNIX_TIMESTAMP('2014-12-31') AS end;

Once you have the values you want, use the script below to update the desired field with a random date.

UPDATE video SET
published = (RAND() * (1419980400 - 1388530800) + 1388530800)
WHERE published = 0;