select version();

CREATE TABLE test ( id INT NOT NULL, dc TIMESTAMPTZ(0) NOT NULL, cs TEXT NOT NULL, ce TEXT NOT NULL, UNIQUE (id, dc), CHECK (cs != ce) );

CREATE INDEX ON test (dc);

INSERT INTO test VALUES (1, '2020-05-27 16:33:52', 'green', 'yellow'), (1, '2020-06-11 20:12:18', 'yellow', 'red'), (1, '2020-06-11 20:20:58', 'red', 'green'), (2, '2020-08-06 14:59:21', 'green', 'yellow'), (2, '2021-03-03 14:31:44', 'yellow', 'red'), (3, '2021-04-28 12:36:45', 'green', 'red');

-- SELECT * FROM test; -- check

-- -- We don't need COALESCE in this case, since the range treats 'NULL' as -INFINITY -- or +INFIITY depending on whether it's at the beginning or end of the range. -- SELECT id, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), cs, ce FROM test;

WITH cte AS ( -- -- We don't need COALESCE in this case, since the range treats 'NULL' as -INFINITY -- or +INFIITY depending on whether it's at the beginning or end of the range. -- SELECT id, LAG(dc) OVER (PARTITION BY id ORDER BY dc) AS lag_dc, cs, dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc) AS lead_dc, ce FROM test ) SELECT c.id, TSTZRANGE(c.lag_dc, c.dc, '[)') AS "Date from:/Date to:", c.cs AS "Colour" FROM cte c WHERE c.lag_dc IS NULL UNION ALL SELECT c.id, TSTZRANGE(c.dc, c.lead_dc, '[)'), c.ce FROM cte c WHERE lead_dc IS NULL UNION ALL SELECT c.id, TSTZRANGE(c.dc, c.lead_dc, '[)'), c.ce FROM cte c WHERE c.lead_dc != 'INFINITY' ORDER BY 1, 2;

CREATE TABLE test_rs -- test of revised schedule ( id INT NOT NULL, df_dt TSTZRANGE NOT NULL, colour TEXT NOT NULL, PRIMARY KEY (id, df_dt), UNIQUE (id, df_dt) );

WITH cte AS ( SELECT id, LAG(dc) OVER (PARTITION BY id ORDER BY dc) AS lag_dc, cs, dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc) AS lead_dc, ce FROM test ) INSERT INTO test_rs SELECT c.id, TSTZRANGE(c.lag_dc, c.dc, '[)') AS "Date from:/Date to:", c.cs AS "Colour" FROM cte c WHERE c.lag_dc IS NULL UNION ALL SELECT c.id, TSTZRANGE(c.dc, c.lead_dc, '[)'), c.ce FROM cte c WHERE lead_dc IS NULL UNION ALL SELECT c.id, TSTZRANGE(c.dc, c.lead_dc, '[)'), c.ce FROM cte c WHERE c.lead_dc != 'INFINITY' ORDER BY 1, 2;

-- -- Records where the beginning and the end of the range falls -- anywhere >= 2021:01:01 00:00:00 -- -- It's bascially a record of any changes in status in 2021! -- SELECT * FROM test_rs WHERE LOWER(df_dt) > '2021-01-01 00:00:00' AND df_dt && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)');

-- -- Status counts at exactly New Year, 2021 - we know that at the point, we had two -- entities with status green and 1 with status yellow -- SELECT colour, COUNT(colour) FROM test_rs WHERE df_dt && TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') GROUP BY colour;