SQLite statement to query for Recurring Calendar Events

Question

I am designing a Calendar Application, which recurrence None, Daily, Weekly, Monthly & Yearly. One of my requirements is that "No two events should be overlapping" Name of the Table where I store the data

Events

fields

dtstart - Event StartTime

dtend - Event End Time

Consider following two case,

Event1 15th Aug 3:00 PM - 4:00 PM Recurrence-None

Event2 15th Aug 2:00 PM - 5-00 PM Recurrence-None

In the above case, 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 15th Aug 3:00 PM - 4:00 PM Recurrence-Daily upto 20th Aug

Event2 18th Aug 2:00 PM - 5-00 PM Recurrence-None

In case two my sqlQuery fail, since it checks the event start and end time for the same date(18th Aug). In my case, my query should show a conflicting time for 15th August.

Please help me with the SQL query, such that even recurring events are checked.

In the events table, I store start time, end time, date of last occurance and occurance type.

Database Scheme is as follows

Table Name : Events

Title | dtstart | dtend | repeat Type | last Occurrence

Answer 1

I'm assuming you want to detect whether a single newly inserted (or updated) event has overlaps (not whether any of of the events already in the database have overlaps), correct?

If so, you could procedurally (in your client language) generate all start/end intervals [s, e] based on the newly inserted event's "repeat Type", and then execute the following query for each of these intervals to detect overlaps (I'm using Oracle syntax here, I'm assuming 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)
    )

Don't expect stellar performance though (especially if your event has large number repetitions or if DTSTART/DTEND are not indexed or SQLite is unable to properly leverage that index).

For performance, you will probably be better off caching all events in memory and doing all processing client-side, which would allow you to more easily use heuristics to "short-circuit" some processing. For example:

• If two events have same "repeat Type", you can just compare their initial intervals without worrying about repetitions - if they don't initially match, they'll never match.
• If one event's "last Occurrence" is before other even't "dtstart", they can never match regardless of "repeat Type".
• Etc...

If you really want all of your processing database-side and you want (query) performance, you are probably looking at some kind of geospatial/multidimensional indexing and you'll need to actually store the event repetitions in the database so they can be indexed, which would probably destroy your insert performance. I'm not familiar with SQLite and whether it supports this kind of indexing...