In YSQL there is the familiar built-in function named RANDOM which generates a random value between 0 (inclusive) and 1 (exclusive).
Although there isn’t a built-in function to generate a random timestamp, we can easily create our own function for that purpose!
CREATE OR REPLACE FUNCTION randomtimestamp(TIMESTAMP, TIMESTAMP)
RETURNS TIMESTAMP AS
$$
DECLARE
ts1 TIMESTAMP:=$1;
ts2 TIMESTAMP:=$2;
BEGIN
RETURN ts1 + random() * (ts2 - ts1);
END;
$$ language 'plpgsql';
Example:
yugabyte=# CREATE OR REPLACE FUNCTION randomtimestamp(TIMESTAMP, TIMESTAMP)
yugabyte-# RETURNS TIMESTAMP AS
yugabyte-# $$
yugabyte$# DECLARE
yugabyte$# ts1 TIMESTAMP:=$1;
yugabyte$# ts2 TIMESTAMP:=$2;
yugabyte$# BEGIN
yugabyte$# RETURN ts1 + random() * (ts2 - ts1);
yugabyte$# END;
yugabyte$# $$ language 'plpgsql';
CREATE FUNCTION
yugabyte=# SELECT randomtimestamp('2023-01-01', '2023-12-31') randomtimestamp_in_2023;
randomtimestamp_in_2023
---------------------------
2023-03-28 18:24:44.96688
(1 row)
yugabyte=# SELECT randomtimestamp(NOW()::TIMESTAMP, NOW()::TIMESTAMP + INTERVAL '10 days') randomtimestamp_within_next_10_days;
randomtimestamp_within_next_10_days
-------------------------------------
2023-01-27 10:00:09.437438
(1 row)
Have Fun!
