PostgreSQL Get a random datetime/timestamp between two datetime/timestamp
You can do almost everything with the date/time operators:
select timestamp '2014-01-10 20:00:00' +
random() * (timestamp '2014-01-20 20:00:00' -
timestamp '2014-01-10 10:00:00')
PostgreSQL Get random value between two timestamps with hours constraint
Converting the output from this answer to
date type, then adding
10:00:00 time (lower hour constraint) and
random interval of up to 8 hours (upper hour constraint) does this pretty quickly:
date (timestamp '2016-01-01' +
random() * (timestamp '2017-12-31' - timestamp '2016-01-01'))
+ time '10:00:00'
+ random() * INTERVAL '8 hours';
Update every row with a random datetime between two dates
Use an expression in place of a query:
set date_created= NOW() + (random() * (NOW()+'-2 days' - NOW())) + '-2 days'
update every row with a different random datetime of a range
strftime('%s', enddate) - strftime('%s', startdate)
you can get the difference in seconds between 2 datetimes.
abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1))
you can get a random integer, greater or equal to
0, that is less than or equal to the difference between the 2 datetimes in seconds.
What you can do is add this random number of seconds to the starting date of your range to create a random datetime within that range:
set datecol = datetime(
abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1)) || ' second'
datecol is the date column in your table
enddate are the boundaries of your range.
Generating time series between two dates in PostgreSQL
Can be done without conversion to/from int (but to/from timestamp instead)
SELECT date_trunc('day', dd):: date
, '1 day'::interval) dd
unique random timestamp in postgresql
The only way to guarantee a unique timestamp is to add a delay between the inserts. In instead you can use a serial
create table mytable (
s serial primary key,
insert into mytable (ts, x, y, z)
500 * RANDOM(),
500 * RANDOM(),
500 * RANDOM()
from generate_series(1, 1000)
How to create a composite datetime with fixed date + random time interval in Postgresql
I would suggest:
select '2020-12-02 08:00:00'::timestamp + random() * interval '2 hour 30 minute'
Or, for the current date, you could express this as:
select current_date + interval '08:00:00' + random() * interval '02:30'
Note that these illustrate two different ways of expressing an interval with 2 hours and 30 minutes (and not even giving
interval '150 minute' as an example).
Update a series of random dates in a Postgres column
You want an
update statement rather than an
insert - and presumably, you want different dates on each row (the time portion does not seem relevant, otherwise you would rather use a
timestamp than a
ids are always increasing, you could just them for increment:
set policy_expires = '2020-07-01'::date + (profile_id::int - 1) * interval '1' day
Otherwise, you can enumerate the rows with
row_number(), then use it to compute the target date:
update renewal_large r
set policy_expires = '2020-07-01'::date + (r1.rn - 1) * interval '1' day
from (select id, row_number() over(order by id) rn from renewal_large) r1
where r1.id = r.id