clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601600 fiddles created (48017 in the last week).

select version();
version
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
 hidden batch(es)


-- -- 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) );
 hidden batch(es)


-- -- 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');
6 rows affected
 hidden batch(es)


-- SELECT * FROM test ORDER BY id, dc; -- check
 hidden batch(es)


-- -- "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;
id lag_dc cs dc lead_dc ce
1 -infinity green 2020-05-27 16:33:52+01 2020-06-11 20:12:18+01 yellow
1 2020-05-27 16:33:52+01 yellow 2020-06-11 20:12:18+01 2020-06-11 20:20:58+01 red
1 2020-06-11 20:12:18+01 red 2020-06-11 20:20:58+01 infinity green
2 -infinity green 2020-08-06 14:59:21+01 2021-03-03 14:31:44+00 yellow
2 2020-08-06 14:59:21+01 yellow 2021-03-03 14:31:44+00 infinity red
3 -infinity green 2021-04-28 12:36:45+01 infinity red
 hidden batch(es)


-- -- 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';
id df dt cs
1 -infinity 2020-05-27 16:33:52+01 green
2 -infinity 2020-08-06 14:59:21+01 green
3 -infinity 2021-04-28 12:36:45+01 green
 hidden batch(es)


-- -- 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';
id dc lead_dc ce
1 2020-06-11 20:20:58+01 infinity green
2 2021-03-03 14:31:44+00 infinity red
3 2021-04-28 12:36:45+01 infinity red
 hidden batch(es)


-- -- 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';
id dc lead_dc ce
1 2020-05-27 16:33:52+01 2020-06-11 20:12:18+01 yellow
1 2020-06-11 20:12:18+01 2020-06-11 20:20:58+01 red
2 2020-08-06 14:59:21+01 2021-03-03 14:31:44+00 yellow
 hidden batch(es)


-- -- 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;
id Date from: Date to: Colour
1 -infinity 2020-05-27 16:33:52+01 green
1 2020-05-27 16:33:52+01 2020-06-11 20:12:18+01 yellow
1 2020-06-11 20:12:18+01 2020-06-11 20:20:58+01 red
1 2020-06-11 20:20:58+01 infinity green
2 -infinity 2020-08-06 14:59:21+01 green
2 2020-08-06 14:59:21+01 2021-03-03 14:31:44+00 yellow
2 2021-03-03 14:31:44+00 infinity red
3 -infinity 2021-04-28 12:36:45+01 green
3 2021-04-28 12:36:45+01 infinity red
 hidden batch(es)


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) );
 hidden batch(es)


CREATE INDEX ON test_rs (ts_from);
 hidden batch(es)


CREATE INDEX ON test_rs (ts_to);
 hidden batch(es)


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;
9 rows affected
 hidden batch(es)


-- SELECT * FROM test_rs ORDER BY id, ts_from; -- check
 hidden batch(es)


-- -- 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;
id ts_from ts_to colour
2 2021-03-03 14:31:44+00 infinity red
3 2021-04-28 12:36:45+01 infinity red
 hidden batch(es)


-- -- 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;
colour count
green 2
yellow 1
 hidden batch(es)