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. 3601594 fiddles created (48011 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 NOT NULL, cs TEXT NOT NULL, ce TEXT NOT NULL, UNIQUE (id, dc) );
 hidden batch(es)


INSERT INTO test VALUES (1, '2020-05-27 16:33:52.000', 'green', 'yellow'), (1, '2020-06-11 20:12:18.000', 'yellow', 'red'), (1, '2020-06-11 20:20:58.000', 'red', 'green'), (2, '2020-08-06 14:59:21.000', 'green', 'yellow'), (2, '2021-03-03 14:31:44.000', 'yellow', 'red'), (3, '2021-04-28 12:36:45.000', 'green', 'red');
6 rows affected
 hidden batch(es)


-- SELECT * FROM test; -- check
 hidden batch(es)


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)


SELECT id, dc AS date_from, TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)'), cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)'), ce FROM test;
id date_from tstzrange cs tstzrange ce
1 2020-05-27 16:33:52+01 (,"2020-05-27 16:33:52+01") green ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01") yellow
1 2020-06-11 20:12:18+01 ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01") yellow ["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01") red
1 2020-06-11 20:20:58+01 ["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01") red ["2020-06-11 20:20:58+01",) green
2 2020-08-06 14:59:21+01 (,"2020-08-06 14:59:21+01") green ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00") yellow
2 2021-03-03 14:31:44+00 ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00") yellow ["2021-03-03 14:31:44+00",) red
3 2021-04-28 12:36:45+01 (,"2021-04-28 12:36:45+01") green ["2021-04-28 12:36:45+01",) red
 hidden batch(es)


SELECT id, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)'), cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)'), ce FROM test;
id lag date_from lead tstzrange cs tstzrange ce
1 2020-05-27 16:33:52+01 2020-06-11 20:12:18+01 (,"2020-05-27 16:33:52+01") green ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01") yellow
1 2020-05-27 16:33:52+01 2020-06-11 20:12:18+01 2020-06-11 20:20:58+01 ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+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 2020-06-11 20:20:58+01 ["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01") red ["2020-06-11 20:20:58+01",) green
2 2020-08-06 14:59:21+01 2021-03-03 14:31:44+00 (,"2020-08-06 14:59:21+01") green ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00") yellow
2 2020-08-06 14:59:21+01 2021-03-03 14:31:44+00 ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00") yellow ["2021-03-03 14:31:44+00",) red
3 2021-04-28 12:36:45+01 (,"2021-04-28 12:36:45+01") green ["2021-04-28 12:36:45+01",) red
 hidden batch(es)


SELECT id, dummy, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, dummy, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb;
id dummy trb cs
1 LAG (,"2020-05-27 16:33:52+01") green
1 LAG ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01") yellow
1 LEAD ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01") yellow
1 LEAD ["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01") red
1 LAG ["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01") red
1 LEAD ["2020-06-11 20:20:58+01",) green
2 LAG (,"2020-08-06 14:59:21+01") green
2 LEAD ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00") yellow
2 LAG ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00") yellow
2 LEAD ["2021-03-03 14:31:44+00",) red
3 LAG (,"2021-04-28 12:36:45+01") green
3 LEAD ["2021-04-28 12:36:45+01",) red
 hidden batch(es)


SELECT DISTINCT * FROM ( SELECT id, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb ) AS t2 ORDER BY id, trb;
id trb cs
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)


-- -- records where the end of the range falls anywhere >= 2021:01:01 00:00:00 -- SELECT * FROM ( SELECT DISTINCT * FROM ( SELECT id, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb ) AS t2 ORDER BY id, trb ) AS t3 WHERE trb && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)');
id trb cs
1 ["2020-06-11 20:20:58+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)


-- -- Status counts at exactly New Year, 2021 -- SELECT cs, COUNT(cs), TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') AS r FROM ( SELECT DISTINCT * FROM ( SELECT id, trb, cs FROM ( SELECT id, 'LAG' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 UNION ALL SELECT id, tra, ce FROM ( SELECT id, 'LEAD' AS dummy, LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc AS date_from, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), TSTZRANGE(LAG(dc) OVER (PARTITION BY id ORDER BY dc), dc, '[)') AS trb, cs, TSTZRANGE(dc, LEAD(dc) OVER (PARTITION BY id ORDER BY dc), '[)') AS tra, ce FROM test ) AS t1 ORDER BY id, trb ) AS t2 ORDER BY id, trb ) AS t3 WHERE trb && TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]') GROUP BY cs, r;
cs count r
green 2 ["2021-01-01 00:00:00+00","2021-01-01 00:00:00+00"]
yellow 1 ["2021-01-01 00:00:00+00","2021-01-01 00:00:00+00"]
 hidden batch(es)