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);
Add a comment
|
4 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;
7 Comments
Ismael Miguel
This is a really old answer and a really old question, but this solved a problem where I had to introduce over 300 random dates. Thank you!
Pacerier
@IsmaelMiguel, This is actually a very indirect way of doing it. Use timestamps stackoverflow.com/a/28944156/632951 , it's more straightforward and less prone to errors.
Álvaro González
@Pacerier - Both methods look fine to me (and quite similar). What potential errors do you have in mind?
Ismael Miguel
@Pacerier Your suggestion is really powerful, but all I wanted was a very basic and quick prototype. The accuracy of the dates wasn't a requirement. I just needed random dates.
Pacerier
@ÁlvaroG.Vicario, When we need to change "past 2 weeks" to something else like "past 2 months" and etc. My solution is more straightforward and scalable to all use cases because it simply specifies a start_date and an end_date.
|
UPDATE mytable
SET col = CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY
This sets col to a date between current date - 13 days and current date; both inclusive, total 14 days.
Comments
Your main problem is that RAND() doesn't allow a range of values like you specify. It will always return a value between 0 and 1.
I can't work out a 1..14 random solution right now, but to get you started, this will pick a random date within the last 10 days:
SET col = DATE(DATE_SUB(NOW(), INTERVAL ROUND(RAND(1)*10) DAY))
1 Comment
degr
RAND(seed), seed is optional. If seed is specified, it returns a repeatable sequence of random numbers. If no seed is specified, it returns a completely random number. So, your RAND(1) will return always same value (at least for MYSQL)One simple option is to use this query:
UPDATE mytable
SET col = (
NOW() - INTERVAL FLOOR(RAND() * 14) DAY
+ INTERVAL FLOOR(RAND() * 23) HOUR
+ INTERVAL FLOOR(RAND() * 59) MINUTE
+ INTERVAL FLOOR(RAND() * 59) SECOND
);
Or, more elegant:
UPDATE mytable
SET col = (NOW() - INTERVAL FLOOR(RAND() * 14 * 24 * 60 * 60) SECOND);
