Как хранить в базе данных даты и временные рамки возникновения событий для быстрого / элегантного запроса?


Предположим, я веду базу данных событий, которая может быть чем угодно: от еженедельного графика работы бизнеса (понедельник-пятница: 10 утра-7 вечера, Сб: 12 вечера-6 вечера, ВС: закрыто) до ежемесячных событий (ярмарка искусств, каждую первую субботу, с 10 утра до 5 вечера) до ежегодного события (благотворительный ужин в канун Рождества, 7 вечера - 9 вечера)

В идеальной ситуации я хотел бы запросить базу данных с любым заданным днем, скажем: Четверг, Январь. 12, 2012, 12 вечера

...и найти все события, которые есть происходящее на:

  • обычный полдень четверга (например, когда бизнес открыт)
  • каждый второй четверг (художественная ярмарка)
  • янв. 12, в частности (Эр...День Памяти Туркменистана)

Я полагаю, что нет смысла говорить о построении запроса, не подумав сначала, как такие даты / времена будут храниться в базе данных.

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

  • Время начала
  • Длительность
  • день недели (например, Пн. Вт., etc)
  • необязательная абсолютная годовая дата (например, 8 мая)
  • необязательное ежемесячное событие (четвертая среда)

И я предполагаю, что нет никакого способа инкапсулировать это в одну строку, верно? Например, бизнес, открытый каждый будний день, будет иметь пять пластинок.

И конечная цель состоит в том, чтобы иметь возможность сделать относительно элегантный запрос, который мог бы найти все записи событий, которые содержат данный момент в их таймфрейме.

2 7

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, вычисление следующего времени запуска).