Pull to refresh

Хранение повторяющихся дат в календаре

При создании планировщиков часто возникает вопрос, как удобно организовать хранение повторяющихся событий в базе данных. На русском материала не нашел, поэтому приведу здесь описание трех способов, конкретно для MySQL.

Данные способы подходят, если вам нужно хранить события, повторяющиеся через регулярные промежутки времени, такие как: каждый день, каждые n дней, каждую неделю, каждый месяц и каждый год.

Предположим, мы храним события в таблице events:

CREATE TABLE `events` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `end_date` datetime,
  `title` varchar(150) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`)
)


1. Дополнительная таблица


Самый простой способ, подойдет если повторяющихся дат бывает не много.
Создаем еще одну таблицу с временем наступления событий:

CREATE TABLE `occurrences` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` int(10) UNSIGNED NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FK_occurrences_events_id FOREIGN KEY (`event_id`)
  REFERENCES `events` (`id`) ON DELETE CASCADE
)


Запрос выборки легкий и самый быстрый.
Выбираем все события за день или за месяц:

SELECT * FROM `events` `e`
  JOIN `occurrences` `o` ON `e`.`id` = `r`.`event_id`
  WHERE `o`.`date` BETWEEN :date AND :next_date;

2. Простой интервал


Создаем таблицу с повторами:

CREATE TABLE `recurrences` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` int(10) UNSIGNED NOT NULL,
  `start` int(10) NOT NULL,
  `interval` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FK_recurrences_events_id FOREIGN KEY (`event_id`)
  REFERENCES `events` (`id`) ON DELETE CASCADE
)


Выбираем все события для каждого дня:

SELECT * FROM `events` `e`
  LEFT JOIN `recurrences` `r` ON `r`.`event_id` = `e`.`id`
  WHERE  (( :curdate_timestamp - `r`.`start`) % `r`.`interval` = 0 )


где curdate_timestamp — целое число, метка времени нужного дня.

3. Сложный шаблон


Этот метод подходит для хранения сложных шаблонов, например: событие повторяется каждый вторник второй недели третьего месяца.

Создаем таблицу с повторами:

CREATE TABLE `complex_recurrences` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` int(10) UNSIGNED NOT NULL,
  `start` int(10) UNSIGNED NOT NULL,
  `weekday` tinyint(4) DEFAULT NULL,
  `week` tinyint(4) DEFAULT NULL,
  `month` tinyint(4) DEFAULT NULL,
  `year` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FK_complex_recurrences_events_id FOREIGN KEY (`event_id`)
  REFERENCES `events` (`id`) ON DELETE CASCADE
)


Выбираем события для каждого дня:

SELECT * FROM `events` `e`
LEFT JOIN `complex_recurrences` `r` ON `r`.`event_id` = `e`.`id`
WHERE  ( 
  (`weekday` = :curweekday OR `weekday` IS NULL)
  AND
  (`week` = :curweek OR `week` IS NULL)
  AND
  (`month` = :curmonth OR `month` IS NULL)
  AND
  (`year` = :curyear OR `year` IS NULL)
  AND `start` >= :curdate
)


Этот метод можно объединить с предыдущим, чтобы охватить больше шаблонов, добавив поле interval и условие

SELECT * FROM `events` `e`
  LEFT JOIN `complex_recurrences` `r` ON `r`.`event_id` = `e`.`id`
  WHERE  (( :curdate_timestamp - `r`.`start`) % `r`.`interval` = 0 ) OR (%current_condition%)
Tags:
Hubs:
You can’t comment this publication because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author’s username will be hidden by an alias.