selecting values in same time period sqlite-CodePudding

Consider Table Events

Date         Time         Duration        Event
1-1-2023     20:28:00     95              A
1-1-2023     20:29:00     60              B
1-1-2023     20:29:55     10              C
1-1-2023     21:12:00     2               D
...

Now i want to get entries happening in same time period, so for example activity A happens from 20:28:00 to 20:29:35, and activity B happens in time period of 20:29:00 to 20:30:00

So Event A and B have common 35 seconds and B and C has common 5 secs.

i want to get all events in common time period, is it possible using just sql query?

CodePudding user response：

Assuming that your dates are in the proper ISO format YYYY-MM-DD, you can use a self join:

SELECT *
FROM events e1 INNER JOIN events e2
ON e2.event > e1.event
AND datetime(e2.date || ' ' || e2.time, e2.duration || ' second') > e1.date || ' ' || e1.time
AND e2.date || ' ' || e2.time < datetime(e1.date || ' ' || e1.time, e1.duration || ' second');

See the demo.

CodePudding user response：

Maybe, supposing you have index on date-time

SELECT *
FROM events e1 INNER JOIN events e2
ON  datetime(e2.date || ' ' || e2.time) 
    BETWEEN (e1.date || ' ' || e1.time)
       AND datetime(e1.date || ' ' || e1.time, e1.duration || ' second')
WHERE e2.event <> e1.event;

As @forpas says, if you kept date an time values in a single column, and have index on this column, performance will be better