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.
This question is related to
database-design
calendar
@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!
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:
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:
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!
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.
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
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:
<?php
require 'connect.php';
$now = strtotime("yesterday");
$pushToFirst = -11;
for($i = $pushToFirst; $i < $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 . "<br />";
echo $week . " " . $weekday . "<br />";
$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 <= DATE('$nowString')
)";
foreach ($dbConnect->query($sql) as $row) {
print $row['ID'] . "\t";
print $row['NAME'] . "<br />";
}
echo "<br /><br /><br />";
}
?>
PHP example code connect.php:
<?
// ----------------------------------------------------------------------------------------------------
// 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->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
// ----------------------------------------------------------------------------------------------------
// / Connecting to database
// ----------------------------------------------------------------------------------------------------
?>
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. :)
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!
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.
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
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.
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.
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.
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.
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 <= 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!
Source: Stackoverflow.com