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?

Другие вопросы по тегам