Select a Random Date Between Two Dates
Moderator: NorbertKrupa
Select a Random Date Between Two Dates
Hi, Can someone help me write a function that will return a random date between two dates? Or if it can't be done in a function maybe just the select statement?
Thank, Fred
Re: Select a Random Date Between Two Dates
Code: Select all
CREATE OR REPLACE FUNCTION randomdate(d1 TIMESTAMP, d2 TIMESTAMP)
RETURN TIMESTAMP
AS BEGIN
RETURN TO_TIMESTAMP(EXTRACT(EPOCH FROM d1) + RANDOMINT(FLOOR(EXTRACT(EPOCH FROM d2) - EXTRACT(EPOCH FROM d1))::INTEGER));
END;
Code: Select all
test_db=> select randomdate('2011-12-31', '2012-01-03');
randomdate
---------------------
2012-01-02 10:13:27
Code: Select all
test_db=> select randomdate('2000-01-01 03:07:13', '2010-12-03 13:43:11');
randomdate
---------------------
2004-04-01 17:06:59
(1 row)
Code: Select all
test_db=> select randomdate('2000-01-01 03:07:13'::date, '2010-12-03 13:43:11'::date);
randomdate
---------------------
2002-07-25 03:58:36
(1 row)
Code: Select all
test_db=> select randomdate('2000-01-01 03:07:13'::date, '2010-12-03 13:43:11'::date)::date;
randomdate
------------
2007-03-19
(1 row)