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. 3601466 fiddles created (47978 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)


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


CREATE INDEX ON test (dc);
 hidden batch(es)


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


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


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;
id Date from:/Date to: Colour
1 (,"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",) green
2 (,"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",) red
3 (,"2021-04-28 12:36:45+01") green
3 ["2021-04-28 12:36:45+01",) red
 hidden batch(es)


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


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;
9 rows affected
 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 LOWER(df_dt) > '2021-01-01 00:00:00' AND df_dt && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)');
id df_dt colour
2 ["2021-03-03 14:31:44+00",) red
3 ["2021-04-28 12:36:45+01",) 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 df_dt && TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') GROUP BY colour;
colour count
green 2
yellow 1
 hidden batch(es)