select version();

-- -- Original table design from the OP -- CREATE TABLE test ( id INT NOT NULL, dc TIMESTAMPTZ(0) NOT NULL, -- all times are xx:yy:zz.000, so use a precision of 0 cs TEXT NOT NULL, ce TEXT NOT NULL, UNIQUE (id, dc), CHECK (cs != ce) );

-- -- OP's data -- 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 ORDER BY id, dc; -- check

-- -- "Foundation" query - using this as a Common Table Expression, we can refactor our -- data - or better yet, refactor our base tables in our system. -- SELECT id, COALESCE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc, cs, dc, COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc), 'INFINITY') AS lead_dc, ce FROM test;

-- -- We get the first record of each set (by id) - from '-INFINITY' to the first -- date_changed (dc) -- WITH cte1 AS ( SELECT id, COALESCE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc, cs, dc, COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc), 'INFINITY') AS lead_dc, ce FROM test ) SELECT c1.id, c1.lag_dc AS df, c1.dc AS dt, c1.cs FROM cte1 c1 WHERE lag_dc = '-INFINITY';

-- -- We get the last record of each set (by id) - from the last date_changed to -- (plus) 'INFINITY' -- WITH cte2 AS ( SELECT id, COALESCE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc, cs, dc, COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc), 'INFINITY') AS lead_dc, ce FROM test ) SELECT c2.id, c2.dc, c2.lead_dc, c2.ce FROM cte2 c2 WHERE lead_dc = 'INFINITY';

-- -- We get the "internal" records of each set (by id) - from date_changed (dc) to -- the next dc (LEAD) - so where the LEAD(dc) IS NOT NULL -- WITH cte3 AS ( SELECT id, COALESCE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc, cs, dc, COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc), 'INFINITY') AS lead_dc, ce FROM test ) SELECT c3.id, c3.dc, c3.lead_dc, c3.ce FROM cte3 c3 WHERE c3.lead_dc != 'INFINITY';

-- -- We now obtain the union of all 3 sets and we have our result! -- WITH cte AS ( SELECT id, COALESCE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc, cs, dc, COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc), 'INFINITY') AS lead_dc, ce FROM test ) SELECT c1.id, c1.lag_dc AS "Date from:", c1.dc AS "Date to:", c1.cs AS "Colour" FROM cte c1 WHERE lag_dc = '-INFINITY' -- first records UNION ALL SELECT c2.id, c2.dc, c2.lead_dc, c2.ce FROM cte c2 WHERE lead_dc = 'INFINITY' -- last records UNION ALL SELECT c3.id, c3.dc, c3.lead_dc, c3.ce FROM cte c3 WHERE c3.lead_dc != 'INFINITY' -- middle records ORDER BY 1, 2;

CREATE TABLE test_rs -- test of revised schedule ( id INT NOT NULL, ts_from TIMESTAMPTZ NOT NULL, ts_to TIMESTAMPTZ NOT NULL, colour TEXT NOT NULL, PRIMARY KEY (id, ts_from), UNIQUE (id, ts_to), CHECK (ts_from < ts_to) );

CREATE INDEX ON test_rs (ts_from);

CREATE INDEX ON test_rs (ts_to);

WITH cte AS ( SELECT id, COALESCE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc, cs, dc, COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc), 'INFINITY') AS lead_dc, ce FROM test ) INSERT INTO test_rs SELECT c1.id, c1.lag_dc AS "Date from:", c1.dc AS "Date to:", c1.cs AS "Colour" FROM cte c1 WHERE lag_dc = '-INFINITY' UNION ALL SELECT c2.id, c2.dc, c2.lead_dc, c2.ce FROM cte c2 WHERE lead_dc = 'INFINITY' UNION ALL SELECT c3.id, c3.dc, c3.lead_dc, c3.ce FROM cte c3 WHERE c3.lead_dc != 'INFINITY' ORDER BY 1, 2;

-- SELECT * FROM test_rs ORDER BY id, ts_from; -- check

-- -- 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 ts_from >= '2021-01-01 00:00:00' ANd ts_to >= '2021-01-01 00:00:00' ORDER BY id, ts_from;

-- -- 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 ts_from <= '2021-01-01 00:00:00' AND ts_to >= '2021-01-01 00:00:00' GROUP BY colour ORDER BY colour;