Sample Header Ad - 728x90

Computing the set difference of tables of intervals

5 votes
2 answers
386 views
I often run into the following problem. I have two tables of intervals. They are bounded by dates (with no time component). Within each table the intervals do not overlap. StartTs | EndTs --|-- 2015-01-03 | 2015-03-02 2015-03-05 | 2015-04-01 StartTs | EndTs --|-- 2015-01-07 | 2015-02-27 2015-03-01 | 2015-03-13 2016-01-01 | 2016-01-02 And I want to find the set difference of the the two tables, i.e., the intervals representing the time in the first table not in the second table. Desired output for the dummy example above: StartTs | EndTs --|-- 2015-01-03 | 2015-01-06 2015-02-28 | 2015-02-28 2015-03-14 | 2015-04-01 i.e. if the first table's dates are marked in yellow below and the ranges from the second table surrounded with a box I'd be looking for the contiguous ranges of unboxed yellow dates. enter image description here I'm currently treating intervals as inclusive on both ends, and using DateTime for my timestamps. My current approach is to take the complement of the second table via a triple self-join (yuck) and then intersecting the result with the first table via a join. Not fun. Is there a better approach?
Asked by Rushabh Mehta (251 rep)
Jan 8, 2022, 11:44 PM
Last activity: Jan 9, 2022, 01:54 PM