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
[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
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.
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
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)
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.
