Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert/ Update random date in MySQL

How would I update a column with a random date in the past 2 weeks using MySQL?

For example (code doesn't actually work):

UPDATE mytable SET col = sysdate() - rand(1, 14); 
like image 715
Blair Avatar asked Dec 28 '10 12:12

Blair


People also ask

How do I generate a random timestamp in SQL?

This should work nicely: SET @MIN = '2010-04-30 14:53:27'; SET @MAX = '2012-04-30 14:53:27'; SELECT TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);

How can we get a random number between 1 and 100 in mysql?

select FLOOR( RAND() * (maximumValue-minimumValue) + minimumValue) as anyVariableName; Let us check with some maximum and minimum value. The maximum value we are considering is 200 and minimum is 100. The random number will be between 100 and 200 including 100 and 200 itself.


2 Answers

You can get a random integer with this expression:

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j - i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5)); 

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_rand

Use that to generate a random number of days, hours or minutes (depending on the resolution) and add that number to current date.

Full expression would be:

-- Date only SELECT CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY; 
-- Date and time SELECT CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 14 * 24 * 60 *60) SECOND; 

Demo

like image 66
Álvaro González Avatar answered Sep 23 '22 13:09

Álvaro González


UPDATE mytable SET col = CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY 

This sets col to a date between (and including) current date and current date - 13 days. Multiply by 15 to get current date - 14 days.

like image 34
Salman A Avatar answered Sep 22 '22 13:09

Salman A