Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


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

 


CREATE TABLE


INSERT INTO status
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,
  42;    -- <<== only one sensor - need to vary this from 1 - 4 (or whatever number).

INSERT 0 7


SELECT * FROM status LIMIT 5; 


start_tz
end_tz
ms
sensor_id


2022-01-01 00:00:00+00
2022-01-01 00:20:00+00
stationary
42

2022-01-01 00:20:00+00
2022-01-01 00:40:00+00
stationary
42

2022-01-01 00:40:00+00
2022-01-01 01:00:00+00
in_motion)
42

2022-01-01 01:00:00+00
2022-01-01 01:20:00+00
stationary
42

2022-01-01 01:20:00+00
2022-01-01 01:40:00+00
stationary
42



SELECT 5

start_tz	end_tz	ms	sensor_id
2022-01-01 00:00:00+00	2022-01-01 00:20:00+00	stationary	42
2022-01-01 00:20:00+00	2022-01-01 00:40:00+00	stationary	42
2022-01-01 00:40:00+00	2022-01-01 01:00:00+00	in_motion)	42
2022-01-01 01:00:00+00	2022-01-01 01:20:00+00	stationary	42
2022-01-01 01:20:00+00	2022-01-01 01:40:00+00	stationary	42