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