SQL record Collision / Overlapping events

I have a table in a database with records that represent events. Among I have several fields that describe the event there are two fields that define the time of an event:

  • StartTime
  • Duration
    The EndTime can be calculated by Adding the Duration to the StartTime.

I want to implement a system where I can do some collision detection: where two events overlap. Now I do that in Xojo code. But when cycling through records of the recordset and checking that record for overlaps, I perform another SQLselect. That doesn’t seem very efficient. Resulting in delays when displaying the records and overlap info.

So, I want to include the collision/overlap detection in the initial SQLselect statement. But I have no idea where to get started. Any ideas?

I running if I understand you well maybe this is what you will need using SQLite as SQL query.

I didn’t have time to test it in Xojo but works in DBMS browser which supporting SQL quering it.

Table name: test
Field 1: started -> stores info when something starts as human time stamp YYYY-MM-dd HH:mm:ss
Field 2: duration -> stores info in sec’s how long it’s take

From this 2 fields using bellow SQL query calc is made and total ended time is placed in fields uxts_total_human and uxts_total.

You have others just to play with it.

SELECT strftime("%s",started) AS uxts , strftime("%s",started)+duration AS uxts_total, datetime(strftime("%s",started)+duration, 'unixepoch', 'localtime') As uxts_total_human, duration, started FROM test

In above query with bellow foo data result is:

Started: 2017-10-02 11:18:30
Duration: 15 sec.

uxts_total: 1506943125
uxts_total_human: 2017-10-02 13:18:45

Hope it will helps you.

[quote=352573:@Bogdan Pavlovic]You have others just to play with it.

SELECT strftime("%s",started) AS uxts , strftime("%s",started)+duration AS uxts_total, datetime(strftime("%s",started)+duration, ‘unixepoch’, ‘localtime’) As uxts_total_human, duration, started FROM test[/quote]

I kinda see what you do. But your code doesn’t give me a recordset of overlapping records. I already do the calculation of the duration in Xojo code. I think that maybe I just wasn’t clear in my initial question :slight_smile:

I just want to test a table against itself and returning a recordset where certain event-records overlap.
Or better, just adding a “calculated field” to the recordset, with a boolean flag, that tells me that the record has overlaps, elsewhere in the table.

After looking at a Stackoverflow page I saw something interesting. The difference is that it looks at two tables.

SELECT * 
FROM table1,table2 
WHERE table2.start <= table1.end 
AND (table2.end IS NULL OR table2.end >= table1.start)

I think I will play around with it for a bit, to see if I can make it work with only one table.


SELECT * 
FROM table1 A  , table1 B
WHERE B.start <= A.end 
AND (B.end IS NULL OR B.end >= A.start)
AND (A.start<>B.start AND a.END<>b.END) 

the last line assumes no two events will ever be IDENTICAL as an attempt to not return the same event reported as overlapping itself
if you have another key to show they are unique, use it

[quote=352598:@Dave S]the last line assumes no two events will ever be IDENTICAL as an attempt to not return the same event reported as overlapping itself
if you have another key to show they are unique, use it[/quote]

Yea, I know. That code was copied from the Stackoverflow page. It was just something that could get me started, I think. I thought I had to so some weird JOIN. But that didn’t seem right.

Also, I have to add something that two of the same records won’t be selected as being overlapping ones. Simply by adding the following in the WHERE part of the statement

b.ID <> a.ID

And actually, the code example does look at one table. It just simply uses the A and B aliases.

[quote=352602:@Edwin van den Akker]Also, I have to add something that two of the same records won’t be selected as being overlapping ones. Simply by adding the following in the WHERE part of the statement

b.ID <> a.ID
And actually, the code example does look at one table. It just simply uses the A and B aliases.[/quote]
both of which is EXACTLY what I said :slight_smile:

SELECT * 
FROM table1 A  , table1 B
WHERE B.start <= A.end 
AND (B.end IS NULL OR B.end >= A.start)
AND (A.id<>b.id) 
1 Like

Exactly…
One of these days I should READ better before answering :wink:
Thanks!

Besides… the code was just code I saw online, as I said before.
I have other fields. My Primary Key is a UUID (text) value. And my time values are numbers in milliseconds, for the StartTime and the Duration values.