PHP/MySQL: Model repeating events in a database but query for date ranges

Question

I'm working on a (what I was intending to be) simple PHP/MySQL app. As part of it I'd like to be able to model repeating events, however I need to be able to query all the events that happened between two dates (including repeated events). The events only have a date, the time of day doesn't matter.

I've been researching this and have looked in to various approaches including Calendar Recurring/Repeating Events - Best Storage Method and Repeating calendar events and some final maths.

However, any example of a database schema supporting this that I find online, only seems to support querying for events that happened on a certain day. There is no support for events that happened between a range of dates.

As an abstract example

Events table (with some sort of repeat representation):

Event   | Start Date   |   Repeats
-------------------------------------
Meeting | 10/Dec/2012  |   Every 7 days
Lunch   | 10/Dec/2012  |   Every 1 days

Target result of abstract query SELECT Events BETWEEN 09/Dec/2012 AND 20/Dec/2012

Event   |  Date        |   Repeats
-------------------------------------
Meeting | 10/Dec/2012  |   Every 7 days
Meeting | 17/Dec/2012  |   Every 7 days
Lunch   | 10/Dec/2012  |   Every 1 days
Lunch   | 11/Dec/2012  |   Every 1 days
Lunch   | 12/Dec/2012  |   Every 1 days
Lunch   | 13/Dec/2012  |   Every 1 days
etc...
Lunch   | 20/Dec/2012  |   Every 1 days

Is there a database schema that will support these kind of queries? How would I go around making a query on that schema for any event (including repeating events) that happened between two days?

Or perhaps a design pattern that is used for repeating events?

This is very hard, and likely to end up with a very complicated schema and or query. Wouldn't it be easier (and more efficient) to construct the resultset in PHP from a simple query of the event table? — Gareth Cornish, Dec 10 '12 at 14:22
Perhaps, but I'm concerned that over time you have the potential problem of having a result set full of stale repeats (or ones that don't occur in your date range) and having to prune these out with the application. — Pez Cuckow, Dec 10 '12 at 15:05

score 5 · Accepted Answer · edited Feb 01 '15 at 12:43

I would create a tally table with just one col called id and fill that table with numbers from 0 to 500. Now we easily use that to make selections instead of using a while loop.

Id
-------------------------------------
0
1
2
etc...

Then i'd store the events in a table with Name as varchar, startdate as datetime and repeats as int

Name    | StartDate            |   Repeats
-------------------------------------
Meeting | 2012-12-10 00:00:00  |   7
Lunch   | 2012-12-10 00:00:00  |   1

Now we can use the tally table to select all dates between two dates by using:

SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
FROM `tally`
WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
ORDER BY Id ASC

ShowDate
-------------------------------------
2012-12-09 00:00:00
2012-12-10 00:00:00
2012-12-11 00:00:00
2012-12-12 00:00:00
2012-12-13 00:00:00
2012-12-14 00:00:00
2012-12-15 00:00:00
2012-12-16 00:00:00
2012-12-17 00:00:00
2012-12-18 00:00:00
2012-12-19 00:00:00
2012-12-20 00:00:00

Then we join this on the events table to calculate the difference between the startdate and the showdate. We devided the results of this by the repeats column and if the remainder is 0, we have match.

All combined becomes:

SELECT E.Id, E.Name, E.StartDate, E.Repeats, A.ShowDate, DATEDIFF(E.StartDate, A.ShowDate) AS diff
FROM events AS E, (
    SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
    FROM `tally`
    WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
    ORDER BY Id ASC
) a
WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0
AND A.ShowDate>=E.StartDate

Which results in

Id  | Name       |StartDate             | Repeats   | ShowDate              | diff
---------------------------------------------------------------------------------
1   | Meeting    | 2012-12-10 00:00:00  | 7         | 2012-12-10 00:00:00   | 0
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-10 00:00:00   | 0
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-11 00:00:00   | -1
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-12 00:00:00   | -2
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-13 00:00:00   | -3
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-14 00:00:00   | -4
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-15 00:00:00   | -5
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-16 00:00:00   | -6
1   | Meeting    | 2012-12-10 00:00:00  | 7         | 2012-12-17 00:00:00   | -7
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-17 00:00:00   | -7
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-18 00:00:00   | -8
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-19 00:00:00   | -9
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-20 00:00:00   | -10

Now you could (and should!) speed things up. For instance by directly storing dates in a table so you can just select all dates directly instead of using a tally table with dateadd. Every thing you can cache and dont have to calculate again is good.

score -3 · Answer 2 · answered Dec 10 '12 at 14:08

-3

I didn't quite understand your goal... Maybe you were looking for UNIQUE function in SQL?

answered Dec 10 '12 at 14:08

MAXIM

1,223
1
9
16

Probably not only I misunderstand some questions but I also don't predict the votes I could receive. Guess unless you know exactly the answer better not to write anything at all, otherwise requesting for more info or suggesting something gets you downvoted :))) – MAXIM Dec 10 '12 at 17:24
That is correct, you can delete this answer to prevent any more downvotes – zeros-and-ones Feb 01 '17 at 22:30

PHP/MySQL: Model repeating events in a database but query for date ranges

2 Answers2

Linked