Calendar Recurring Repeating Events - Best Storage Method

Question

I am building a custom events system  and if you have a repeating event that looks like this   Event A repeats every 4 days starting on March 3  2011  or   Event B repeats every 2 weeks on Tuesday starting on March 1  2011  How can I store that in a Database in a way that would make it simple to lookup  I don t want performance issues if there are a large number of events  and I have to go through each and every one when rendering the calendar

User · Answer

The two examples you ve given are very simple  they can be represented as a simple interval  the first being four days  the second being 14 days   How you model this will depend entirely on the complexity of your recurrences  If what you have above is truly that simple  then store a start date and the number of days in the repeat interval   If  however  you need to support things like     Event A repeats every month on the 3rd of the month starting on March 3  2011   Or      Event A repeats second Friday of the month starting on March 11  2011   Then that s a much more complex pattern

User · Answer

I developed an esoteric programming language just for this case  The best part about it is that it is schema less and platform independent  You just have to write a selector program  for your schedule  syntax of which is constrained by the set of rules described here -   https   github com tusharmath sheql wiki Rules  The rules are extendible and you can add any sort of customization based on the kind of repetition logic you want to perform  without worrying about schema migrations etc   This is a completely different approach and might have some disadvantages of its own

User · Answer

I would follow this guide  https   github com bmoeskau Extensible blob master recurrence-overview md  Also make sure you use the iCal format so not to reinvent the wheel and  remember Rule  0  Do NOT store individual recurring event instances as rows in your database

User · Answer

For all of you who are interested in this  now you can just copy and paste to get started within minutes  I took the advice in the comments as well as I could  Let me know if I m missing something    COMPLEX VERSION    events    ---------- ----------------    ID         NAME               ---------- ----------------    1          Sample event 1     2          Second  event      3          Third event       ---------- ----------------    events meta    ---- ---------- -------------- ------------------ ------------- -------------- ------------ ------------- ----------------    ID   event id   repeat start   repeat interval    repeat year   repeat month   repeat day   repeat week   repeat weekday    ---- ---------- -------------- ------------------ ------------- -------------- ------------ ------------- ----------------    1    1          2014-07-04     7                  NULL          NULL           NULL         NULL          NULL               2    2          2014-06-26     NULL               2014                                      2             5                  3    3          2014-07-04     NULL                                                                       5                 ---- ---------- -------------- ------------------ ------------- -------------- ------------ ------------- ----------------    SQL code   CREATE TABLE IF NOT EXISTS  events       ID  int 11  NOT NULL AUTO INCREMENT     NAME  varchar 255  NOT NULL    PRIMARY KEY   ID     ENGINE MyISAM  DEFAULT CHARSET utf8 AUTO INCREMENT 7    -- -- Dumping data for table  events  --  INSERT INTO  events    ID    NAME   VALUES  1   Sample event     2   Another event     3   Third event        CREATE TABLE IF NOT EXISTS  events meta       ID  int 11  NOT NULL AUTO INCREMENT     event id  int 11  NOT NULL     repeat start  date NOT NULL     repeat interval  varchar 255  NOT NULL     repeat year  varchar 255  NOT NULL     repeat month  varchar 255  NOT NULL     repeat day  varchar 255  NOT NULL     repeat week  varchar 255  NOT NULL     repeat weekday  varchar 255  NOT NULL    PRIMARY KEY   ID      UNIQUE KEY  ID    ID     ENGINE MyISAM  DEFAULT CHARSET utf8 AUTO INCREMENT 6    -- -- Dumping data for table  events meta  --  INSERT INTO  events meta    ID    event id    repeat start    repeat interval    repeat year    repeat month    repeat day    repeat week    repeat weekday   VALUES  1  1   2014-07-04    7    NULL    NULL    NULL    NULL    NULL     2  2   2014-06-26    NULL    2014              2    5     3  3   2014-07-04    NULL                        1      also available as MySQL export  for easy access   PHP example code index php    lt  php     require  connect php             now   strtotime  yesterday          pushToFirst   -11      for  i    pushToFirst   i  lt   pushToFirst 30   i                   now   strtotime      i   day             year   date  Y    now            month   date  m    now            day   date  d    now            nowString    year    -     month    -     day           week    int    date  d    now  - 1    7    1           weekday   date  N    now            echo  nowString     lt br   gt            echo  week          weekday     lt br   gt                sql    SELECT EV                   FROM  events  EV                 RIGHT JOIN  events meta  EM1 ON EM1  event id    EV  id                  WHERE   DATEDIFF    nowString   repeat start     repeat interval   0                   OR                         repeat year    year OR repeat year                             AND                      repeat month    month OR repeat month                             AND                      repeat day    day OR repeat day                             AND                      repeat week    week OR repeat week                             AND                      repeat weekday    weekday OR repeat weekday                             AND repeat start  lt   DATE   nowString                               foreach   dbConnect- gt query  sql  as  row                print  row  ID       t               print  row  NAME       lt br   gt                       echo   lt br   gt  lt br   gt  lt br   gt            gt    PHP example code connect php    lt      ----------------------------------------------------------------------------------------------------                                          Connecting to database    ----------------------------------------------------------------------------------------------------    Database variables  username        password        hostname         database            Try to connect to database and set charset to UTF8 try        dbConnect   new PDO  mysql host  hostname dbname  database charset utf8    username   password        dbConnect- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION      catch PDOException  e        echo  ERROR       e- gt getMessage         ----------------------------------------------------------------------------------------------------                                           Connecting to database    ----------------------------------------------------------------------------------------------------   gt    Also the php code is available here  for better readability   index php and connect php Now setting this up should take you minutes  Not hours

User · Answer

While the currently accepted answer was a huge help to me  I wanted to share some useful modifications that simplify the queries and also increase performance      Simple  Repeat Events  To handle events which recur at regular intervals  such as   Repeat every other day    or   Repeat every week on Tuesday    You should create two tables  one called events like this   ID    NAME 1     Sample Event 2     Another Event   And a table called events meta like this   ID    event id      repeat start       repeat interval 1     1             1369008000         604800            -- Repeats every Monday after May 20th 2013 1     1             1369008000         604800            -- Also repeats every Friday after May 20th 2013   With repeat start being a unix timestamp date with no time  1369008000 corresponds to May 20th 2013    and repeat interval an amount in seconds between intervals  604800 is 7 days       By looping over each day in the calendar you can get repeat events using this simple query   SELECT EV   FROM  events  EV RIGHT JOIN  events meta  EM1 ON EM1  event id    EV  id  WHERE     1299736800 - repeat start    repeat interval   0     Just substitute in the unix-timestamp  1299736800  for each date in your calendar   Note the use of the modulo    sign   This symbol is like regular division  but returns the   remainder   instead of the quotient  and as such is 0 whenever the current date is an exact multiple of the repeat interval from the repeat start   Performance Comparison  This is significantly faster than the previously suggested  meta keys -based answer  which was as follows   SELECT EV   FROM  events  EV RIGHT JOIN  events meta  EM1 ON EM1  event id    EV  id  RIGHT JOIN  events meta  EM2 ON EM2  meta key    CONCAT   repeat interval    EM1  id    WHERE EM1 meta key    repeat start      AND             CASE   1299132000 - EM1  meta value                WHEN 0               THEN 1             ELSE   1299132000 - EM1  meta value              END             EM2  meta value          1   If you run EXPLAIN this query  you ll note that it required the use of a join buffer    ---- ------------- ------- -------- --------------- --------- --------- ------------------ ------ --------------------------------    id   select type   table   type     possible keys   key       key len   ref                rows   Extra                             ---- ------------- ------- -------- --------------- --------- --------- ------------------ ------ --------------------------------     1   SIMPLE        EM1     ALL      NULL            NULL      NULL      NULL                  2   Using where                         1   SIMPLE        EV      eq ref   PRIMARY         PRIMARY   4         bcs EM1 event id      1                                       1   SIMPLE        EM2     ALL      NULL            NULL      NULL      NULL                  2   Using where  Using join buffer    ---- ------------- ------- -------- --------------- --------- --------- ------------------ ------ --------------------------------    The solution with 1 join above requires no such buffer      Complex  Patterns  You can add support for more complex types to support these types of repeat rules   Event A repeats every month on the 3rd of the month starting on March 3  2011   or  Event A repeats second Friday of the month starting on March 11  2011   Your events table can look exactly the same   ID    NAME 1     Sample Event 2     Another Event   Then to add support for these complex rules add columns to events meta like so   ID    event id      repeat start       repeat interval    repeat year    repeat month    repeat day    repeat week    repeat weekday 1     1             1369008000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Monday after May 20  2013 1     1             1368144000         604800             NULL           NULL            NULL          NULL           NULL             -- Repeats every Friday after May 10  2013 2     2             1369008000         NULL               2013                                         2              5                -- Repeats on Friday of the 2nd week in every month       Note that you simply need to either specify a repeat interval or a set of repeat year  repeat month  repeat day  repeat week  and repeat weekday data   This makes selection of both types simultaneously very simple  Just loop through each day and fill in the correct values   1370563200 for June 7th 2013  and then the year  month  day  week number and weekday as follows    SELECT EV   FROM  events  EV RIGHT JOIN  events meta  EM1 ON EM1  event id    EV  id  WHERE     1370563200 - repeat start    repeat interval   0     OR         repeat year   2013 OR repeat year             AND      repeat month   6 OR repeat month             AND      repeat day   7 OR repeat day             AND      repeat week   2 OR repeat week             AND      repeat weekday   5 OR repeat weekday             AND repeat start  lt   1370563200       This returns all events that repeat on the Friday of the 2nd week  as well as any events that repeat every Friday  so it returns both event ID 1 and 2   ID    NAME 1     Sample Event 2     Another Event    Sidenote in the above SQL I used PHP Date s default weekday indexes  so  5  for Friday     Hope this helps others as much as the original answer helped me

User · Answer

Rogue Coder  This is great   You could simply use the modulo operation  MOD or   in mysql  to make your code simple at the end   Instead of   AND         CASE   1299132000 - EM1  meta value            WHEN 0           THEN 1         ELSE   1299132000 - EM1  meta value          END         EM2  meta value      1   Do    current timestamp   1299132000    AND      current timestamp  - EM1  meta value    MOD EM2  meta value     1     To take this further  one could include events that do not recur for ever    Something like  repeat interval 1 end  to denote the date of the last  repeat interval 1  could be added  This however  makes the query more complicated and I can t really figure out how to do this      Maybe someone could help

User · Answer

Sounds very much like MySQL events that are stored in system tables  You can look at the structure and figure out which columns are not needed      EVENT CATALOG  NULL     EVENT SCHEMA  myschema       EVENT NAME  e store ts          DEFINER  jon ghidora       EVENT BODY  SQL EVENT DEFINITION  INSERT INTO myschema mytable VALUES  UNIX TIMESTAMP          EVENT TYPE  RECURRING       EXECUTE AT  NULL   INTERVAL VALUE  5   INTERVAL FIELD  SECOND         SQL MODE  NULL           STARTS  0000-00-00 00 00 00             ENDS  0000-00-00 00 00 00           STATUS  ENABLED    ON COMPLETION  NOT PRESERVE          CREATED  2006-02-09 22 36 06     LAST ALTERED  2006-02-09 22 36 06    LAST EXECUTED  NULL    EVENT COMMENT

User · Answer

Storing  Simple  Repeating Patterns  For my PHP MySQL based calendar  I wanted to store repeating recurring event information as efficiently as possibly  I didn t want to have a large number of rows  and I wanted to easily lookup all events that would take place on a specific date   The method below is great at storing repeating information that occurs at regular intervals  such as every day  every n days  every week  every month every year  etc etc  This includes every Tuesday and Thursday type patterns as well  because they are stored separately as every week starting on a Tuesday and every week starting on a Thursday   Assuming I have two tables  one called events like this   ID    NAME 1     Sample Event 2     Another Event   And a table called events meta like this   ID    event id      meta key           meta value 1     1             repeat start       1299132000 2     1             repeat interval 1  432000   With repeat start being a date with no time as a unix timestamp  and repeat interval an amount in seconds between intervals  432000 is 5 days     repeat interval 1 goes with repeat start of the ID 1  So if I have an event that repeats every Tuesday and every Thursday  the repeat interval would be 604800  7 days   and there would be 2 repeat starts and 2 repeat intervals  The table would look like this   ID    event id      meta key           meta value 1     1             repeat start       1298959200 -- This is for the Tuesday repeat 2     1             repeat interval 1  604800 3     1             repeat start       1299132000 -- This is for the Thursday repeat 4     1             repeat interval 3  604800 5     2             repeat start       1299132000 6     2             repeat interval 5  1          -- Using 1 as a value gives us an event that only happens once   Then  if you have a calendar that loops through every day  grabbing the events for the day it s at  the query would look like this   SELECT EV   FROM  events  EV RIGHT JOIN  events meta  EM1 ON EM1  event id    EV  id  RIGHT JOIN  events meta  EM2 ON EM2  meta key    CONCAT   repeat interval    EM1  id    WHERE EM1 meta key    repeat start      AND             CASE   1299132000 - EM1  meta value                WHEN 0               THEN 1             ELSE   1299132000 - EM1  meta value              END             EM2  meta value          1 LIMIT 0   30   Replacing  current timestamp  with the unix timestamp for the current date  Minus the time  so the hour  minute and second values would be set to 0    Hopefully this will help somebody else too     Storing  Complex  Repeating Patterns  This method is better suited for storing complex patterns such as   Event A repeats every month on the 3rd of the month starting on March 3  2011   or   Event A repeats Friday of the 2nd week of the month starting on March 11  2011  I d recommend combining this with the above system for the most flexibility  The tables for this should like like   ID    NAME 1     Sample Event 2     Another Event   And a table called events meta like this   ID    event id      meta key           meta value 1     1             repeat start       1299132000 -- March 3rd  2011 2     1             repeat year 1        3     1             repeat month 1       4     1             repeat week im 1   2 5     1             repeat weekday 1   6   repeat week im represents the week of the current month  which could be between 1 and 5 potentially  repeat weekday in the day of the week  1-7   Now assuming you are looping through the days weeks to create a month view in your calendar  you could compose a query like this   SELECT EV     FROM  events  AS EV JOIN  events meta  EM1 ON EM1 event id   EV id AND EM1 meta key    repeat start  LEFT JOIN  events meta  EM2 ON EM2 meta key   CONCAT   repeat year    EM1 id   LEFT JOIN  events meta  EM3 ON EM3 meta key   CONCAT   repeat month    EM1 id   LEFT JOIN  events meta  EM4 ON EM4 meta key   CONCAT   repeat week im    EM1 id   LEFT JOIN  events meta  EM5 ON EM5 meta key   CONCAT   repeat weekday    EM1 id   WHERE     EM2 meta value  2011   OR EM2 meta value         AND     EM3 meta value  4   OR EM3 meta value         AND     EM4 meta value  2   OR EM4 meta value         AND     EM5 meta value  6   OR EM5 meta value         AND EM1 meta value  gt    current timestamp  LIMIT 0   30   This combined with the above method could be combined to cover most repeating recurring event patterns  If I ve missed anything please leave a comment

User · Answer

Enhancement  replace timestamp with date As a small enhancement to the accepted answer that was subsequently refined by ahoffner - it is possible to use a date format rather than timestamp   The advantages are   readable dates in the database no issue with the years  gt  2038 and timestamp removes need to be careful with timestamps that are based on seasonally adjusted dates i e  in the UK 28th June starts one hour earlier than 28th December so deriving a timestamp from a date can break the recursion algorithm   to do this  change the DB repeat start to be stored as type  date  and repeat interval now hold days rather than seconds   i e  7 for a repeat of 7 days  change the sql line  WHERE    1370563200 - repeat start    repeat interval   0    to  WHERE   DATEDIFF   2013-6-7   repeat start     repeat interval   0   everything else remains the same  Simples

User · Answer

Why not use a mechanism similar to Apache cron jobs  http   en wikipedia org wiki Cron  For calendar scheduling I d use slightly different values for  bits  to accommodate standard calendar reoccurence events - instead of   day of week  0 - 7   month  1 - 12   day of month  1 - 31   hour  0 - 23   min  0 - 59    -- I d use something like   Year  repeat every N years   month  1 - 12   day of month  1 - 31   week of month  1-5   day of week  0 - 7    Hope this helps

User · Answer

While the proposed solutions work  I was trying to implement with Full Calendar and it would require over 90 database calls for each view  as it loads current  previous  and next month   which  I wasn t too thrilled about   I found an recursion library https   github com tplaner When where you simply store the rules in the database and one query to pull all the relevant rules   Hopefully this will help someone else  as I spent so many hours trying to find a good solution   Edit  This Library is for PHP

User · Answer

RRULE standard is built for exactly this requirement i e  saving and understanding recurrences  Microsoft and google both use it in their calendar events  Please go through this document for more details  https   icalendar org iCalendar-RFC-5545 3-8-5-3-recurrence-rule html

[database-design] Calendar Recurring/Repeating Events - Best Storage Method

Examples related to database-design

Examples related to calendar