PHP/MySQL: модель повторяющихся событий в базе данных, но запрос диапазонов дат
Я работаю над (как я и предполагал) простым приложением PHP/MySQL. Как часть этого я хотел бы иметь возможность моделировать повторяющиеся события, однако мне нужно иметь возможность запрашивать все события, которые произошли между двумя датами (включая повторные события). У событий есть только дата, время дня не имеет значения.
Я изучал это и изучал различные подходы, включая повторяющиеся / повторяющиеся события календаря - лучший метод хранения и повторяющиеся события календаря, а также некоторые последние математические расчеты.
Тем не менее, любой пример схемы базы данных, поддерживающей это, которую я нахожу в сети, кажется, только поддерживает запросы на события, которые произошли в определенный день. Нет поддержки событий, которые произошли между диапазонами дат.
В качестве абстрактного примера
Таблица событий (с некоторым видом повторного представления):
Event | Start Date | Repeats
-------------------------------------
Meeting | 10/Dec/2012 | Every 7 days
Lunch | 10/Dec/2012 | Every 1 days
Целевой результат абстрактного запроса 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
Существует ли схема базы данных, которая будет поддерживать такие запросы? Как мне сделать запрос к этой схеме для любого события (включая повторяющиеся события), которое произошло между двумя днями?
Или, возможно, шаблон дизайна, который используется для повторения событий?
2 ответа
Я бы создал таблицу подсчета с одним столбцом id
и заполните эту таблицу числами от 0 до 500. Теперь мы легко можем использовать это для выбора, вместо использования цикла while.
Id
-------------------------------------
0
1
2
etc...
Тогда я буду хранить события в таблице с Name as varchar
, startdate as datetime
а также repeats as int
Name | StartDate | Repeats
-------------------------------------
Meeting | 2012-12-10 00:00:00 | 7
Lunch | 2012-12-10 00:00:00 | 1
Теперь мы можем использовать таблицу подсчета, чтобы выбрать все даты между двумя датами, используя:
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
Затем мы присоединяем это к таблице событий, чтобы вычислить разницу между начальной датой и выставочной датой. Мы разделили результаты этого на repeats
столбец и если остаток 0
Матч
Все вместе становится:
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
Что приводит к
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
Теперь вы можете (и должны!) Ускорить процесс. Например, путем непосредственного сохранения дат в таблице, чтобы вы могли просто выбрать все даты напрямую, а не использовать таблицу подсчета с dateadd. Все, что вы можете кэшировать и не нужно вычислять заново, это хорошо.
Я не совсем понял вашу цель... Может быть, вы искали уникальную функцию в SQL?