randomize timestamp in postgresql

December 21st, 2009 | Tags:

The following may be useful for test data or if you need to get data into a table that has a timestamp column included in the primary key requirement.

Create a function that will allow you to specify a random number range (taken from the http://wiki.postgresql.org/wiki/Random_Range):

CREATE OR REPLACE FUNCTION random(numeric, numeric
RETURNS numeric AS $$
SELECT ($1 + ($2 - $1) * random())::numeric;
$$ LANGUAGE 'sql' VOLATILE;

I then used it to generate a range of timestamps for today:

select timestamp '2009-12-21' + random(0,86400) * interval '1 second' as timestamp;
Comments are closed.