Календарь повторяющихся / повторяющихся событий - лучший способ хранения
Я строю систему пользовательских событий, и если у вас есть повторяющееся событие, которое выглядит так:
Событие А повторяется каждые 4 дня, начиная с 3 марта 2011 г.
или же
Событие B повторяется каждые 2 недели во вторник, начиная с 1 марта 2011 г.
Как я могу сохранить это в базе данных таким образом, чтобы упростить поиск. Я не хочу проблем с производительностью, если есть большое количество событий, и мне приходится проходить все до конца при рендеринге календаря.
13 ответов
Хранение "простых" повторяющихся паттернов
Для моего календаря на основе PHP/MySQL я хотел хранить информацию о повторяющихся / повторяющихся событиях настолько эффективно, насколько это возможно. Я не хотел иметь большое количество строк, и я хотел легко искать все события, которые будут происходить в определенную дату.
Приведенный ниже метод отлично подходит для хранения повторяющейся информации, которая происходит через регулярные промежутки времени, например, каждый день, каждые n дней, каждую неделю, каждый месяц, каждый год и т. Д. И т. Д. Это также включает шаблоны типов каждый вторник и четверг, поскольку они хранятся отдельно, как каждую неделю, начиная со вторника, и каждую неделю, начиная с четверга.
Предполагая, что у меня есть две таблицы, одна называется events
как это:
ID NAME
1 Sample Event
2 Another Event
И стол называется events_meta
как это:
ID event_id meta_key meta_value
1 1 repeat_start 1299132000
2 1 repeat_interval_1 432000
С repeat_start является дата без времени в качестве метки времени unix, а repeat_interval - количество секунд в интервалах (432000 - 5 дней).
repeat_interval_1 идет с repeat_start идентификатора 1. Так что, если у меня есть событие, которое повторяется каждый вторник и каждый четверг, repeat_interval будет 604800 (7 дней), и будет 2 repeat_starts и 2 repeat_intervals. Таблица будет выглядеть так:
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
Затем, если у вас есть календарь, который просматривает каждый день, собирая события за день, в котором он находится, запрос будет выглядеть так:
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
Замена {current_timestamp}
с меткой времени unix для текущей даты (минус время, поэтому значения часов, минут и секунд будут установлены на 0).
Надеюсь, это поможет кому-то еще!
Хранение "сложных" повторяющихся паттернов
Этот метод лучше подходит для хранения сложных шаблонов, таких как
Event A repeats every month on the 3rd of the month starting on March 3, 2011
или же
Event A repeats Friday of the 2nd week of the month starting on March 11, 2011
Я бы порекомендовал объединить это с вышеуказанной системой для большей гибкости. Таблицы для этого должны выглядеть так:
ID NAME
1 Sample Event
2 Another Event
И стол называется events_meta
как это:
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
представляет неделю текущего месяца, которая может быть от 1 до 5 потенциально. repeat_weekday
в день недели 1-7.
Теперь, предположив, что вы просматриваете дни / недели для создания представления месяца в календаре, вы можете составить запрос, подобный следующему:
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 >= {current_timestamp}
LIMIT 0 , 30
Это в сочетании с вышеописанным способом может быть объединено для охвата большинства повторяющихся / повторяющихся шаблонов событий. Если я что-то пропустил, пожалуйста, оставьте комментарий.
Хотя принятый в настоящее время ответ мне очень помог, я хотел бы поделиться некоторыми полезными изменениями, которые упрощают запросы и повышают производительность.
"Простые" повторяющиеся события
Для обработки событий, которые повторяются через равные промежутки времени, такие как:
Repeat every other day
или же
Repeat every week on Tuesday
Вы должны создать две таблицы, одна называется events
как это:
ID NAME
1 Sample Event
2 Another Event
И стол называется events_meta
как это:
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
С repeat_start
дата Unix без отметки времени (1369008000 соответствует 20 мая 2013 г.), и repeat_interval
количество в секундах между интервалами (604800 - 7 дней).
Зацикливая каждый день в календаре, вы можете получать повторяющиеся события, используя этот простой запрос:
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE (( 1299736800 - repeat_start) % repeat_interval = 0 )
Просто замените в unix-timestamp (1299736800) каждую дату в вашем календаре.
Обратите внимание на использование по модулю (знак%). Этот символ похож на обычное деление, но возвращает "остаток" вместо частного, и поэтому он равен 0, когда текущая дата является точным кратным повторения повторения_интервала из repeat_start.
Сравнение производительности
Это значительно быстрее, чем ранее предложенный ответ на основе meta_keys, который был следующим:
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
Если вы запустите EXPLAIN для этого запроса, вы заметите, что он требует использования буфера соединения:
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| 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 |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
Решение с 1 объединением выше не требует такого буфера.
"Сложные" Узоры
Вы можете добавить поддержку более сложных типов для поддержки следующих типов правил повторения:
Event A repeats every month on the 3rd of the month starting on March 3, 2011
или же
Event A repeats second Friday of the month starting on March 11, 2011
Ваша таблица событий может выглядеть точно так же:
ID NAME
1 Sample Event
2 Another Event
Затем, чтобы добавить поддержку этих сложных правил, добавьте столбцы в events_meta
вот так:
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
Обратите внимание, что вам просто нужно указать repeat_interval
или набор repeat_year
, repeat_month
, repeat_day
, repeat_week
, а также repeat_weekday
данные.
Это делает выбор обоих типов одновременно очень простым. Просто переберите каждый день и введите правильные значения (1370563200 на 7 июня 2013 года, а затем год, месяц, день, номер недели и день недели следующим образом):
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
)
Это возвращает все события, которые повторяются в пятницу 2-й недели, а также любые события, которые повторяются каждую пятницу, поэтому он возвращает оба идентификатора события 1 и 2:
ID NAME
1 Sample Event
2 Another Event
* Sidenote в приведенном выше SQL Я использовал индексы дня недели по умолчанию для PHP Date, поэтому "5" для пятницы
Надеюсь, это поможет другим так же, как мне помог оригинальный ответ!
Улучшение: заменить метку времени датой
В качестве небольшого улучшения принятого ответа, который был впоследствии уточнен ahoffner - можно использовать формат даты, а не метку времени. Преимущества:
- читаемые даты в базе данных
- нет проблем с годами> 2038 и отметкой времени
- при удалении необходимо соблюдать осторожность с временными метками, основанными на сезонно скорректированных датах, т. е. в Великобритании 28 июня начинается на час раньше, чем 28 декабря, поэтому получение временной метки из даты может нарушить алгоритм рекурсии.
для этого измените БД repeat_start
храниться в виде типа "дата" и repeat_interval
теперь держите дни, а не секунды. т.е. 7 для повторения 7 дней.
изменить строку sql: ГДЕ ((1370563200 - repeat_start)% repeat_interval = 0)
to: WHERE (DATEDIFF ('2013-6-7', event_start)% repeat_interval = 0)
все остальное остается прежним. Simples!
Я бы следовал этому руководству: https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md
Также убедитесь, что вы используете формат iCal, чтобы не изобретать велосипед и помнить Правило № 0:НЕ храните отдельные повторяющиеся экземпляры событий в виде строк в вашей базе данных!
Для всех вас, кто заинтересован в этом, теперь вы можете просто скопировать и вставить, чтобы начать работу в течение нескольких минут. Я принял совет в комментариях, как мог. Дайте мне знать, если я что-то упустил.
"КОМПЛЕКСНАЯ ВЕРСИЯ":
События
+----------+----------------+ | ID | ИМЯ | +----------+----------------+ | 1 | Пример события 1 | | 2 | Второе событие | | 3 | Третье событие | + ---------- + ---------------- +
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:
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');
также доступный как экспорт MySQL (для легкого доступа)
Пример PHP-кода 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-кода 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
// ----------------------------------------------------------------------------------------------------
?>
Также здесь доступен код php (для лучшей читаемости):
index.php
а также
connect.php
Теперь настройка займет несколько минут. Не часы.:)
Хотя предложенные решения работают, я пытался реализовать их с помощью полного календаря, и для каждого представления потребовалось бы более 90 обращений к базе данных (так как он загружает текущий, предыдущий и следующий месяц), что меня не очень обрадовало.
Я нашел рекурсивную библиотеку https://github.com/tplaner/When когда вы просто сохраняете правила в базе данных и один запрос, чтобы получить все соответствующие правила.
Надеюсь, это поможет кому-то еще, так как я провел много часов, пытаясь найти хорошее решение.
Изменить: эта библиотека для PHP
Почему бы не использовать механизм, похожий на работу Apache cron? http://en.wikipedia.org/wiki/Cron
Для календаря \ планирования я бы использовал немного отличающиеся значения для "битов", чтобы приспособить стандартные события повторения календаря - вместо [день недели (0–7), месяц (1–12), день месяца (1–31), час (0–23), мин (0–59)]
- Я бы использовал что-то вроде [Год (повторять каждые N лет), месяц (1–12), день месяца (1–31), неделя месяца (1–5), день недели (0–7)]
Надеюсь это поможет.
Стандарт RRULE создан именно для этого требования, т.е. сохранения и понимания повторов. Microsoft и Google используют его в своих календарных мероприятиях. Пожалуйста, просмотрите этот документ для получения более подробной информации. https://icalendar.org/iCalendar-RFC-5545/3-8-5-3-recurrence-rule.html
Я разработал эзотерический язык программирования только для этого случая. Самое приятное в этом то, что он не требует схем и не зависит от платформы. Вам просто нужно написать программу выбора, для вашего расписания, синтаксис которой ограничен набором правил, описанных здесь -
https://github.com/tusharmath/sheql/wiki/Rules
Правила являются расширяемыми, и вы можете добавить любую настройку в зависимости от того, какую логику повторения вы хотите выполнить, не беспокоясь о переносе схемы и т. Д.
Это совершенно другой подход и может иметь свои недостатки.
Очень похоже на события MySQL, которые хранятся в системных таблицах. Вы можете посмотреть на структуру и выяснить, какие столбцы не нужны:
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:
@Rogue Coder
Это замечательно!
Вы можете просто использовать операцию по модулю (MOD или% в mysql), чтобы сделать ваш код простым в конце:
Вместо:
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
Делать:
$current_timestamp = 1299132000 ;
AND ( ('$current_timestamp' - EM1.`meta_value` ) MOD EM2.`meta_value`) = 1")
Чтобы пойти дальше, можно включить события, которые не повторяются вечно.
Может быть добавлено что-то вроде "repeat_interval_1_end" для обозначения даты последнего "repeat_interval_1". Это, однако, делает запрос более сложным, и я не могу понять, как это сделать...
Может быть, кто-то может помочь!
Два приведенных вами примера очень просты; они могут быть представлены в виде простого интервала (первый - четыре дня, второй - 14 дней). То, как вы смоделируете это, будет полностью зависеть от сложности ваших повторений. Если то, что у вас есть, действительно так просто, сохраните дату начала и количество дней в интервале повторения.
Если, однако, вам нужно поддерживать такие вещи, как
Событие А повторяется каждый месяц 3-го числа месяца, начинающегося 3 марта 2011 г.
Или же
Событие А повторяется вторая пятница месяца, начиная с 11 марта 2011 г.
Тогда это гораздо более сложный паттерн.
установить @dat_ini = '2023-05-20',@dat_fim = '2022-11-20'; выберите (DATEDIFF(@dat_fim,@dat_ini)) % 60
ЭТО < 10
Он работает только в течение короткого периода.
Для этого нужно взять дату начала и изменить Месяц, который на экране и добавить год, затем вычесть его из даты начала, тогда работает.