sqlpostgresql

How to set a timestamptz column in postgres to a range of values to simulate real world data?

Basically I have a created_at column. All entries are created today because they were inserted today.

In order to finish developing a feature, I would like to simulate real world data, with lots of different created_at values, spanning the last 3 years.

eg.

-- for all records
update sales_events set created_at = ... ???

I don't really care about the time portion of the timestamptz too much. I also don't care if some of the dates repeat. I just need the dates spread out across a few years. Eg. 2020 to 2024. Anywhere in there.

Solution

  • RANDOM() will generate random floats and multiply it with dates across last 3 years

    Fiddle

    UPDATE events
SET created_at = created_at - INTERVAL '3 years' * RANDOM();