Postgresql Get a Random Datetime/Timestamp Between Two Datetime/Timestamp

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:

select 
  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:

update my_table 
set date_created= NOW() + (random() * (NOW()+'-2 days' - NOW())) + '-2 days'

update every row with a different random datetime of a range

With:

strftime('%s', enddate) - strftime('%s', startdate)

you can get the difference in seconds between 2 datetimes.

With:

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:

update tablename 
set datecol = datetime(
  startdate, 
  abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1)) || ' second'
)

datecol is the date column in your table

startdate and 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
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '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,
    ts timestamp,
    x int,
    y int,
    z int
);
insert into mytable (ts, x, y, z) 
select 
    clock_timestamp(),
    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 date).

If your ids are always increasing, you could just them for increment:

update renewal_large
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

---

Related Topics