Find none overlapping intervals using sqlite

I have a data base with ids and time intervals example:

I want to a query which select none-overlapping intervals.Rows with different TimeId are considered non-overlapping even if the range [start, end] from both overlaps. So expected output from the previous example would be

Example database

CREATE TABLE "test" (
    "timeid" INTEGER,
    "start"  INTEGER,
    "end"    INTEGER
);

INSERT INTO test VALUES(1, 0, 10)
INSERT INTO test VALUES(1, 2, 13)
INSERT INTO test VALUES(1, 11, 21)
INSERT INTO test VALUES(1, 15, 30)

INSERT INTO test VALUES(2, 0, 10)
INSERT INTO test VALUES(2, 2, 13)
INSERT INTO test VALUES(2, 11, 21)
INSERT INTO test VALUES(2, 15, 30)

Last thing which gave hope was

with recursive nol(id, start, end) AS (
  select test.timeid, test.start, test.end from test inner join 
 ( select timeid as ttid, min(start) as ttstart from test group by id)  as tt on
 tt.ttid = test.timeid and tt.ttstart = test.start
    union all 
    select test.timeid , test.start, test.end from test  inner join nol 
    on test.timeid = nol.id and test.start > nol.end    
    GROUP By test.timeid
    Having min(test.start)
)
select * from nol 

Assumptions and Problems

In the above code (and in the real case that I have), as you may noticed, i assume that the combination timeid and start are unique, i.e., no two rows shoud have the same timeid and start.

The problem with the above code is that it is not supported. I get the error (Result: recursive aggregate queries not supported).

Could you please help what would be an alternative to using aggregate inside recursive?

thanks in advance

You need to sign in to view this answers