Select a Random Date Between Two Dates

[fsalvelt]

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?

[id10t]

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;

test:

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)

[fsalvelt]

PERFECT! Thanks, skwa!!!