Home > front end >  PostgreSQL - GENERATE_SERIES issue - simulating data
PostgreSQL - GENERATE_SERIES issue - simulating data

Time:09-27

I thought I understood GENERATE_SERIES(), but obviously not as well as I thought.

I have a table:

CREATE TABLE status
(
  start_tz  TIMESTAMPTZ NOT NULL,
  end_tz    TIMESTAMPTZ NOT NULL,
  ms        TEXT        NOT NULL,
  sensor_id SMALLINT    NOT NULL,
);

And I wish to fill this table with simulated data from 2022-01-01 00:00:00 in 20 minute intervals for 4 sensors (numbered 1 - 4) for 2 hours (or whatever - no hardcoding). The ms text is random - see below.

I have tried various combinations of

SELECT
  GENERATE_SERIES('2022-01-01 00:00:00', '2022-01-01 02:00:00', (INTERVAL '20 MINUTES')),
  GENERATE_SERIES('2022-01-01 00:20:00', '2022-01-01 02:20:00', (INTERVAL '20 MINUTES')),
  CASE
     WHEN random() > 0.5 THEN 'in_motion)'
    ELSE                     'stationary'
  END,
  GENERATE_SERIES(1, 4);

and just can't get it. I also have various cominations and permutations of FROM GENERATE_SERIES(1, ... whatever guess...); and I'm stumped.

What I want looks like this:

start_tz                 end_tz                 ms      sensor_id
2022-01-01 00:00:00 00  2022-01-01 00:20:00 00  stationary  1
2022-01-01 00:20:00 00  2022-01-01 00:40:00 00  stationary  1
2022-01-01 00:40:00 00  2022-01-01 01:00:00 00  in_motion)  1
2022-01-01 01:00:00 00  2022-01-01 01:20:00 00  in_motion)  1
2022-01-01 01:20:00 00  2022-01-01 01:40:00 00  stationary  1
2022-01-01 01:40:00 00  2022-01-01 02:00:00 00  in_motion)  1
2022-01-01 02:00:00 00  2022-01-01 02:20:00 00  stationary  1
2022-01-01 00:00:00 00  2022-01-01 00:20:00 00  stationary  2
2022-01-01 00:20:00 00  2022-01-01 00:40:00 00  stationary  2
2022-01-01 00:40:00 00  2022-01-01 01:00:00 00  in_motion)  2
...
...
and so on

A fiddle is available enter image description here

  • Related