Как хранить абсолютные и относительные диапазоны дат в базе данных SQL?

Я пытаюсь смоделировать концепцию DateRange для приложения для создания отчетов. Некоторые диапазоны дат должны быть абсолютными, с 1 марта 2011 г. по 31 марта 2011 г. Другие диапазоны относятся к текущей дате, последним 30 дням, следующей неделе и т. д. Как лучше всего хранить эти данные в таблице SQL?

Очевидно, что для абсолютных диапазонов у меня могут быть значения BeginDate и EndDate. Для относительных диапазонов имеет смысл иметь InceptionDate и целочисленный столбец RelativeDays. Как мне включить обе эти идеи в одну таблицу без внедрения в нее контекста, т.е. упомянуть все четыре столбца и использовать логику XOR для заполнения 2 из 4.

Две возможные схемы, которые я отклонил из-за наличия контекстно-зависимых столбцов:

CREATE TABLE DateRange
(
    BeginDate DATETIME NULL,
    EndDate DATETIME NULL,
    InceptionDate DATETIME NULL,
    RelativeDays INT NULL
)

OR

CREATE TABLE DateRange
(
    InceptionDate DATETIME NULL,
    BeginDaysRelative INT NULL,
    EndDaysRelative INT NULL
)

Спасибо за любой совет!


sql schema date-range
person ant    schedule 30.09.2011    source источник

Ответы (4)


arrow_upward
3
arrow_downward

Я не понимаю, почему ваш второй дизайн не соответствует вашим потребностям, если только вы не находитесь в лагере «никаких NULL никогда». Просто оставьте InceptionDate NULL для выбора «относительно текущей даты», чтобы ваше приложение могло отличить их от фиксированных диапазонов дат.

(Примечание: не зная вашего механизма БД, я оставил математику даты и текущие проблемы с датой в псевдокоде. Кроме того, как и в вашем вопросе, я не учел любое текстовое описание и столбцы первичного ключа).

Затем либо создайте такое представление:

 CREATE VIEW DateRangesSolved (Inception, BeginDays, EndDays) AS
    SELECT CASE WHEN Inception IS NULL THEN Date() ELSE Inception END,
           BeginDays,
           EndDays,
    FROM DateRanges

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

Вы даже можете сделать еще один шаг вперед:

 CREATE VIEW DateRangesSolved (BeginDate, EndDate) AS
    SELECT (CASE WHEN Inception IS NULL THEN Date() ELSE Inception END + BeginDays),
           (CASE WHEN Inception IS NULL THEN Date() ELSE Inception END + EndDays)
    FROM DateRanges
person Larry Lustig    schedule 30.09.2011
comment
Я не религиозен в отношении NULL, но я ненавижу контекстуальные столбцы в RDBS, т.е. столбец A заполняется только тогда, когда столбец B имеет значение null, а столбец C представляет разные вещи в зависимости от заполнения A или B. - person ant; 30.09.2011
comment
Что ж, здесь вам представлены две сущности, которые одновременно разные и одинаковые. Разница в том, что у одного из двух есть дополнительные данные, связанные с ним. Вам понадобится либо одна таблица с этим небольшим изменением, либо две разные таблицы. - person Larry Lustig; 30.09.2011
comment
Мне нравится идея объединить таблицу хранения с NULL, представляющим текущую дату, а затем использовать представление «Решено», которое соответствующим образом отображает эти даты. Спасибо! - person ant; 03.10.2011

arrow_upward
1
arrow_downward

Другие относятся к текущей дате, последним 30 дням, следующей неделе и т. д. Как лучше всего хранить эти данные в таблице SQL?

Если вы храните эти диапазоны в таблице, вам придется обновлять их каждый день. В этом случае вам придется каждый день обновлять каждую строку по-разному. Это может быть большой проблемой; может и нет.

Обычно в такой таблице не так много строк, часто меньше 50. Структура таблицы очевидна. Обновление должно запускаться заданием cron (или его эквивалентом), и вы должны каждый день запускать очень привередливые отчеты об исключениях, чтобы убедиться, что все было обновлено правильно.

Обычно такие отчеты не должны давать никаких результатов, если все в порядке. У вас есть дополнительная сложность, заключающаяся в том, что создание такого отчета из cron не приведет к выводу, если cron не запущен. И это не нормально.

Вы также можете создать представление, которое не требует обслуживания. С несколькими десятками строк это может быть медленнее, чем физическая таблица, но все же достаточно быстро. И устраняет все работы по обслуживанию и администрированию этих диапазонов. (Проверьте на наличие ошибок, не связанных друг с другом, потому что я этого не делал.)

create view relative_date_ranges as 
select 'Last 30 days' as range_name, 
        (current_date - interval '30' day)::date as range_start, 
        current_date as range_end
union all
select 'Last week' as range_name, 
       (current_date - interval '7' day)::date as range_start, 
       current_date as range_end
union all 
select 'Next week' as range_name, 
       (current_date + interval '7' day)::date as range_start, 
       current_date as range_end

В зависимости от приложения вы можете относиться к своим «абсолютным» диапазонам одинаково.

...
union all
select 'March this year' as range_name, 
       (extract(year from current_date) || '-03-01')::date as range_start, 
       (extract(year from current_date) || '-03-31')::date as range_end
person Mike Sherrill 'Cat Recall'    schedule 30.09.2011

arrow_upward
0
arrow_downward

Поместите их в отдельные таблицы. Нет абсолютно никакой причины помещать их в одну таблицу.

Для относительных дат я бы зашел так далеко, что просто сделал таблицу параметрами, необходимыми для функций даты, т.е.

CREATE TABLE RelativeDate
(
    Id INT Identity,
    Date_Part varchar(25),
    DatePart_Count int
)

Тогда вы сможете узнать, что это -2 WEEK или 30 DAY дисперсия, и использовать это в своей логике.

Если вам нужно увидеть их оба одновременно, вы можете ЛОГИЧЕСКИ объединить их в запросе или представлении без необходимости портить структуру данных, втискивая разные элементы данных в одну и ту же таблицу.

person JNK    schedule 30.09.2011
comment
Ну, я хотел бы использовать это как FK, чтобы таблица отчета содержала макет и определение. В этом случае использование двух таблиц становится еще более сложным, не так ли? - person ant; 30.09.2011
comment
@ant - не совсем так, просто сделайте его из двух частей FK в другой таблице. Range или Relative и идентификатор. Тогда вы все еще можете навязывать отношения, но вам не нужно помещать все в одну таблицу. - person JNK; 30.09.2011

arrow_upward
0
arrow_downward

Создайте таблицу, содержащую дату начала и смещение. Точность смещения зависит от вас.

CREATE TABLE DateRange(
    BeginDate DATETIME NOT NULL,
    Offset int NOT NULL,
    OffsetLabel varchar(100)
)

вставить в него:

INSERT INTO DateRange (BeginDate, Offset, OffsetLabel)
 select '20110301', DATEDIFF(sec, '20110301', '20110331'), 'March 1, 2011 - March 31, 2011'

Последние 30 дней

 INSERT INTO DateRange (BeginDate, Duration, OffsetLabel)
 select '20110301', DATEDIFF(sec, current_timestamp, DATEADD(day, -30, current_timestamp)), 'Last 30 Days'

Чтобы отобразить значения позже:

select BeginDate, EndDate = DATEADD(sec, Offset, BeginDate), OffsetLabel
from DateRange

Если вы хотите иметь возможность анализировать «исходные» расплывчатые описания, вам придется искать функцию «Нечеткая дата» или «Приблизительно». (В исходном коде git существует что-то вроде this.)

person Filip De Vos    schedule 30.09.2011