How to store absolute and relative date ranges in SQL database?

Question

I'm trying to model a DateRange concept for a reporting application. Some date ranges need to be absolute, March 1, 2011 - March 31, 2011. Others are relative to current date, Last 30 Days, Next Week, etc. What's the best way to store that data in SQL table?

Obviously for absolute ranges, I can have a BeginDate and EndDate. For relative ranges, having an InceptionDate and an integer RelativeDays column makes sense. How do I incorporate both of these ideas into a single table without implementing context into it, ie have all four columns mentioned and use XOR logic to populate 2 of the 4.

Two possible schemas I rejected due to having context-driven columns:

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
)

Thanks for any advice!

Answer 1

I don't see why your second design doesn't meet your needs unless you're in the "no NULLs never" camp. Just leave InceptionDate NULL for "relative to current date" choices so that your application can tell them apart from fixed date ranges.

(Note: not knowing your DB engine, I've left date math and current date issues in pseudocode. Also, as in your question, I've left out any text description and primary key columns).

Then, either create a view like this:

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

or just use that logic when you SELECT from the table directly.

You can even take it one step further:

 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

Answer 2

Others are relative to current date, Last 30 Days, Next Week, etc. What's the best way to store that data in SQL table?

If you store those ranges in a table, you have to update them every day. In this case, you have to update each row differently every day. That might be a big problem; it might not.

There usually aren't many rows in that kind of table, often less than 50. The table structure is obvious. Updating should be driven by a cron job (or its equivalent), and you should run very picky exception reports every day to make sure things have been updated correctly.

Normally, these kinds of reports should produce no output if things are fine. You have the added complication that driving such a report from cron will produce no output if cron isn't running. And that's not fine.

You can also create a view, which doesn't require any maintenance. With a few dozen rows, it might be slower than a physical table, but it might still fast enough. And it eliminates all maintenance and administrative work for these ranges. (Check for off-by-one errors, because I didn't.)

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

Depending on the app, you might be able to treat your "absolute" ranges the same way.

...
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

Answer 3

Put them in separate tables. There is absolutely no reason to have them in a single table.

For the relative dates, I would go so far as to simply make the table the parameters you need for the date functions, i.e.

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

Then you can know that it is -2 WEEK or 30 DAY variance and use that in your logic.

If you need to see them both at the same time, you can combine them LOGICALLY in a query or view without needing to mess up your data structure by cramming different data elements into the same table.

Answer 4

Create a table containing the begindate and the offset. The precision of the offset is up to you to decide.

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

to insert into it:

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

Last 30 days

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

To display the values later:

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

If you want to be able to parse the "original" vague descriptions you will have to look for a "Fuzzy Date" or "Approxidate" function. (There exists something like this in the git source code. )