Как хранить в базе данных даты и временные рамки возникновения событий для быстрого / элегантного запроса?
Предположим, я веду базу данных событий, которая может быть чем угодно: от еженедельного графика работы бизнеса (понедельник-пятница: 10 утра-7 вечера, Сб: 12 вечера-6 вечера, ВС: закрыто) до ежемесячных событий (ярмарка искусств, каждую первую субботу, с 10 утра до 5 вечера) до ежегодного события (благотворительный ужин в канун Рождества, 7 вечера - 9 вечера)
В идеальной ситуации я хотел бы запросить базу данных с любым заданным днем, скажем: Четверг, Январь. 12, 2012, 12 вечера
...и найти все события, которые есть происходящее на:
- обычный полдень четверга (например, когда бизнес открыт)
- каждый второй четверг (художественная ярмарка)
- янв. 12, в частности (Эр...День Памяти Туркменистана)
Я полагаю, что нет смысла говорить о построении запроса, не подумав сначала, как такие даты / времена будут храниться в базе данных.
Я не могу представить себе, как концепция регулярных еженедельных рабочих часов (даже игнорируя крайние случаи) может быть смоделирована в одна запись, одно поле, которое также моделировало бы событие, происходящее раз в год. По крайней мере, мне кажется, что мне нужно как минимум пять полей:
- Время начала
- Длительность
- день недели (например, Пн. Вт., etc)
- необязательная абсолютная годовая дата (например, 8 мая)
- необязательное ежемесячное событие (четвертая среда)
И я предполагаю, что нет никакого способа инкапсулировать это в одну строку, верно? Например, бизнес, открытый каждый будний день, будет иметь пять пластинок.
И конечная цель состоит в том, чтобы иметь возможность сделать относительно элегантный запрос, который мог бы найти все записи событий, которые содержат данный момент в их таймфрейме.
2 ответа:
Не уверен, что вы специально просите СУБД-агностическое решение, во всяком случае, в postgresql (и я думаю, что это возможно в большинстве СУБД) вы можете хранить метки времени и получать из них много информации: http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html .
PostgreSQL-специфичный ответ
9.9.1. Извлечение, date_part
EXTRACT (поле из источника) функция extract извлекает подполя например, год или час от даты / времени ценности. источник должен быть значением выражение типа timestamp, time или interval. (Выражения типа дата приводится к метке времени и поэтому может использоваться также.) поле является идентификатором или строкой, которая выбирает, какое поле извлечь из исходное значение. Функция extract возвращает значения типа double точность. Допустимыми именами полей являются следующие: [...] день, ДОУ (день недели), Дой (день года), [...]
Так, например, чтобы выбрать события, происходящие каждый 2-й четверг вы могли бы написать:
SELECT * FROM events #your table WHERE EXTRACT(DOW FROM date_column)=4 # DOW goes from sunday (0) to saturday (6) AND EXTRACT(DAY FROM date_column)>7 # must occurr after 7th day of the month (ie. if the 1st is a Thursday AND EXTRACT(DAY FROM date_column)<15 # and before the 15th (ie. if the 1st is a Friday, so the first Thursday is on the 7th, and the second is on the 14th)
Для моделирования длительности вы можете просто иметь 2 поля временных меток, для начала/конца события.
Также обратите внимание, что вы можете добавлять или вычитать временные метки, а также знать, перекрываются ли интервалы.В общем , я бы сначала попытался использовать типы данных и функции, предоставляемые вашей СУБД, и только если вы действительно не можете найти решение, попробуйте смоделировать его самостоятельно.
Если вы поддерживаете следующее время начала/окончания для каждого события, то запрос прост: выберите все события, где время начала
Как эффективно поддерживать такую таблицу, зависит от вашего приложения. Смотрите, как реализуются существующие приложения типа calendar / todo / cron. Например, вы можете периодически или перед каждым запросом обновлять все события, которые имеют свое конечное время в прошлом.
Преимущество этого подхода заключается в том, что вы можете использовать произвольный комплекс логика для вычисления следующего времени начала / окончания.
И я предполагаю, что нет никакого способа инкапсулировать это в одну строку, верно?
Вы можете хранить одно поле: "правило", которое описывает на языке, похожем на crontab, когда происходит событие (пример: анализ записи crontab, вычисление следующего времени запуска).