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. 3601581 fiddles created (47986 in the last week).

--Create table, insert data create table stack_question ( effective_from timestamp, archived timestamp, cal double precision ); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-12 00:00:00.000000', '2020-01-13 00:00:00.000000', 3.7); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-13 00:00:00.000000', '2020-01-14 00:00:00.000000', 4.2); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-14 00:00:00.000000', '2020-01-15 00:00:00.000000', 7.2); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-13 00:00:00.000000', '2020-01-15 00:00:00.000000', 8.6); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-15 00:00:00.000000', '2020-01-16 00:00:00.000000', 7.2); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-16 00:00:00.000000', '2020-01-17 00:00:00.000000', 11.2); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-17 00:00:00.000000', '2020-01-18 00:00:00.000000', 13.6); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-13 00:00:00.000000', '2020-01-19 00:00:00.000000', 42.2); INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-19 00:00:00.000000', '2020-01-20 00:00:00.000000', 12.3); --Data were're dealing with SELECT effective_from, archived, cal FROM stack_question ORDER BY archived ASC; --Intended result: A query that returns a table with only the rows that don't overlap in time. --In case of a conflict, use the most recently archived row. --Can be achieved manually by: (SELECT * FROM stack_question WHERE effective_from <= '12-Jan-2020' ORDER BY archived DESC LIMIT 1) --First 'effective_from' value from above select UNION (SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1) --Second 'effective_from' UNION (SELECT * FROM stack_question WHERE effective_from <= '14-Jan-2020' ORDER BY archived DESC LIMIT 1) --And so on.. UNION (SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1) UNION (SELECT * FROM stack_question WHERE effective_from <= '15-Jan-2020' ORDER BY archived DESC LIMIT 1) UNION (SELECT * FROM stack_question WHERE effective_from <= '16-Jan-2020' ORDER BY archived DESC LIMIT 1) UNION (SELECT * FROM stack_question WHERE effective_from <= '17-Jan-2020' ORDER BY archived DESC LIMIT 1) UNION (SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1) UNION (SELECT * FROM stack_question WHERE effective_from <= '19-Jan-2020' ORDER BY archived DESC LIMIT 1) ORDER BY effective_from ASC; --So the first row covers the time period Jan-12 to 13 uniquely. --The following 6 rows are all superceded by row 8, which covers the time period Jan-13 to Jan-19, --so disregard them in the final output. --Row 9 covers the unique time period Jan-19 to 20.
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
effective_from archived cal
2020-01-12 00:00:00 2020-01-13 00:00:00 3.7
2020-01-13 00:00:00 2020-01-14 00:00:00 4.2
2020-01-14 00:00:00 2020-01-15 00:00:00 7.2
2020-01-13 00:00:00 2020-01-15 00:00:00 8.6
2020-01-15 00:00:00 2020-01-16 00:00:00 7.2
2020-01-16 00:00:00 2020-01-17 00:00:00 11.2
2020-01-17 00:00:00 2020-01-18 00:00:00 13.6
2020-01-13 00:00:00 2020-01-19 00:00:00 42.2
2020-01-19 00:00:00 2020-01-20 00:00:00 12.3
effective_from archived cal
2020-01-12 00:00:00 2020-01-13 00:00:00 3.7
2020-01-13 00:00:00 2020-01-19 00:00:00 42.2
2020-01-19 00:00:00 2020-01-20 00:00:00 12.3
 hidden batch(es)