SQLite statement to query repeating calendar events

Question

I am developing a calendar application that repeats None, Daily, Weekly, Monthly and Yearly. One of my requirements is that "There are no two events that should overlap." The name of the table in which the data is stored.

Events

fields

dtstart - StartTime event

dtend - Event end time

Consider two cases:

Event1 August 15, 3:00 PM - 4:00 PM Repeat-No

Event2 August 15, 2:00 PM - 5-00 PM Repeat-No

In the above example, the following SQL query works like charm

String sqlQuery = "SELECT * FROM Events WHERE dtstart AND dtend BETWEEN% d AND% d";

sqlQuery = String.format (sqlQuery, dtstart, dtend);

Now consider case two.

Event1 August 15, 3:00 p.m. - 4:00 p.m. Repeat-Daily until August 20

Event2 August 18, 2:00 PM - 5-00 PM Repeat-No

In case two of my sqlQuery fail, as it checks the start and end time of the event on the same date (August 18). In my case, my request should show the conflict time on August 15th.

Please help me with the SQL query, so even repeated events are checked.

In the events table I save the start time, end time, date of the last type of occurrence and occurrence.

The database schema is as follows

Table Name: Events

Heading | dtstart | dtend | repeat type | Last incident

Answer 1

I assume that you want to determine if one recently inserted (or updated) event overlaps ( doesn’t it , does any of the events already exist in the database)?

If so, you can procedurally (in your client language) generate all the start and end intervals [s, e] based on the newly inserted "repeat Type" event, and then run the following query for each of these intervals to detect overlap (I use Oracle syntax is here, I assume SQLite is similar):

 -- A time interval must be either completely "to the left" or completely -- "to the right" of the other time interval for them not to overlap. SELECT * FROM EVENT WHERE NOT( (:s < DTSTART AND :s < DTEND AND :e < DTSTART AND :e < DTEND) OR (:s > DTSTART AND :s > DTEND AND :e > DTSTART AND :e > DTEND) ) 

Do not expect stellar performance (especially if your event has a large number of repetitions or DTSTART / DTEND is not indexed, or SQLite cannot correctly use this index).

For performance, you will probably be better off caching all events in memory and doing all the processing on the client side, which will allow you to more easily use the heuristic to “short circuit” some processing. For instance:

• If two events have the same “repeat type”, you can simply compare their start intervals without worrying about repetitions - if they do not match, they will never match.
• If one event "last occurrence" is in front of another, even "dtstart", they will never be able to match regardless of "repeat Type".
• Etc ...

If you really want all your processing to be on the database side and which you want (query), you are probably looking at some kind of geospatial / multidimensional indexing, and you will need to actually keep the repetitions of events in the database so that they could be indexed, which would probably ruin the performance of your insert. I am not familiar with SQLite and does it support this type of indexing ...

Answer 2

I can’t come up with a single SQL statement that checks for event matching with repetition, but here are some tips.

• If all of your recurring events have a specific end date, you can create an EventInstance table consisting of EventID , StartTime and EndTime . Then you write AFTER INSERT , AFTER UPDATE and AFTER DELETE Triggers in the Event table to update all instances stored in EventInstance . Then your query can be used in the EventInstance table. But tbh, I have no experience with SQLite, so I don’t know if it supports triggers.

• Write a stored procedure in the database to check it with the procedural code (again, if SQLite supports it)

• Check for overlapping events in Java code.

Answer 3

It will be some kind of code. I will describe a check for the weekly re-occupation event A, which begins before the event "none" of entry B.

• Make your check as if both were no.
• If they do not overlap, add 7 days to A.dtstart and dtend.
• Check again.
• Repeat until verification succeeds or A.dtstart> B.dtend.

Use variants of this for daily, montly, etc. If both events coincide with the same schedule, this also works.

If they are on different ones, you need to have a second outer loop that iterates over another interval. The stop condition can be tricky, I think it will be something like the least common multiple of both interval sizes in days.

All this code must be executed in your host language, which can be used as the stored procedure language in SQlite directly. How this is done depends on the host language.