Explain Codes LogoExplain Codes Logo

Insert/ Update random date in MySQL

sql
randomization
date-functions
mysql-queries
Alex KataevbyAlex Kataev·Dec 27, 2024
⚡TLDR

Quickly insert or update a random date between two specific dates in a MySQL table using a blend of ADDDATE(), RAND(), and DATEDIFF():

//Insert a random date reminiscent of a Fortune Cookie surprise
INSERT INTO tbl (date_col) VALUES (ADDDATE('2020-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2023-12-31', '2020-01-01')) DAY));

//Update an existing date by playing Russian Roulette with randomness!
UPDATE tbl SET date_col = ADDDATE('2020-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2023-12-31', '2020-01-01')) DAY) WHERE condition;

Just replace '2020-01-01' and '2023-12-31' with your start and end dates.

Diving deep into date randomness

Let's roll up our sleeves and go ankle-deep into the world of random dates and MySQL functions.

Random past dates

Update a column with a random date within the last two weeks. It's like playing hide-and-seek with time!

//Two weeks ago feels like yesterday, eh?
UPDATE your_table SET your_date_column = CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY;

Random timestamp insertion

To bring in the big guns of granularity, insert a random timestamp.

//Is it a bird? Is it a plane? No, it's just a random timestamp!
INSERT INTO your_table (your_datetime_column) VALUES (CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 14 * 24 * 60 * 60) SECOND);

Rounding strategies for randomness

Instead of FLOOR(), you can use ROUND() to round to the nearest whole number.

//Why fear rounding when we can ROUND it off!
INSERT INTO your_table (your_date_column) VALUES (ADDDATE(CURRENT_DATE, INTERVAL ROUND(RAND() * 14) DAY));

Random future dates

Did you say future? Let's accommodate by tweaking with DATE_ADD().

//Beware! This might turn you into a Time Traveler!
UPDATE your_table SET your_date_column = DATE_ADD(NOW(), INTERVAL FLOOR(RAND() * 14) DAY);

System time utilization

For getting the system's current time, sysdate() comes to the rescue.

//A random date stamp, hand-stamped by your system!
INSERT INTO your_table (your_datetime_column) VALUES (sysdate() - INTERVAL FLOOR(RAND() * 14) DAY);

Advanced usage: Beyond two weeks

Expanding horizons, let's navigate through broader date ranges using RAND() and INTERVAL.

Working with a custom date range

You are the master of your timeline. Update your date difference to suit your range!

//Generating a random date is like throwing a dart on the calendar !
INSERT INTO tbl (date_col) VALUES (ADDDATE('2010-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2023-12-31', '2010-01-01')) DAY));

Keeping your dates fresh!

Regularly updating the dates with a touch of randomness, gives life to the monotony!

//Keeping things fresh, like a daily brewed coffee! 
UPDATE tbl SET date_col = NOW() - INTERVAL FLOOR(RAND() * 365) DAY WHERE some_condition;

Flexing the right function

When spreading your wings of randomization, fly high with NOW(), INTERVAL, RAND() and FLOOR().

Safety checks

Before the leap, have a look! Inserting or updating random dates include critical checks:

  • Never set dates beyond logical boundaries.
  • Beware of randomization conflicting with business rules.

Navigating the rapids: Pitfalls and best practices

Randomizing dates in MySQL is like discovering an untamed river. Here are some things to check while you're rafting:

Date collisions

Ahoy! Beware of date collisions – two records may end up with the same date!

Leap years and date sanity

Mind the leap years when using ADDDATE(), or your date might take a leap of insanity!

Data consistency

sysdate() and now() in the same dish might upset the consistency of your data, pick one!

Time zone trifles

Don't let differences in time zone trip you over, be mindful of the host server's locale.

explain-codes / Sql / Insert/ Update random date in MySQL
Linked

How to update rows with a random date

Sql Populate table with random data

Mysql add days to a date

Mysql insert random datetime in a given datetime range

Postgresql Get a random datetime/timestamp between two datetime/timestamp

Mysql SELECT last few days?

Datetime equal or greater than today in MySQL

Diving deep into date randomnessAdvanced usage: Beyond two weeksFlexing the right functionNavigating the rapids: Pitfalls and best practices
Linked

How to update rows with a random date

Sql Populate table with random data

Mysql add days to a date

Mysql insert random datetime in a given datetime range

Postgresql Get a random datetime/timestamp between two datetime/timestamp

Mysql SELECT last few days?

Datetime equal or greater than today in MySQL