如何在 SQL 数据库中存储绝对和相对日期范围？

Question

我正在尝试为报告应用程序建模 DateRange 概念。某些日期范围需要是绝对的，2011 年 3 月 1 日至 2011 年 3 月 31 日。其他日期范围是相对于当前日期、过去 30 天、下周等。在 SQL 表中存储该数据的最佳方式是什么？

显然，对于绝对范围，我可以有一个 BeginDate 和 EndDate。对于相对范围，拥有 InceptionDate 和整数relativedays 列是有意义的。如何将这两个想法合并到一个表中而不在其中实现上下文，即提到的所有四列并使用 XOR 逻辑填充 4 列中的 2 个。

由于具有上下文驱动列，我拒绝了两种可能的模式：

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

或者

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

谢谢如有任何建议！

Answer 1

我不明白为什么你的第二个设计不能满足你的需求，除非你属于“绝不为空”阵营。只需将 InceptionDate 保留为 NULL 作为“相对于当前日期”的选择，以便您的应用程序可以区分它们与固定日期范围。

（注意：不知道您的数据库引擎，我将日期数学和当前日期问题保留在伪代码中。另外，正如您的问题一样，我省略了任何文本描述和主键列）。

然后，创建一个如下所示的视图：

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

或者直接从表中进行 SELECT 时使用该逻辑。

您甚至可以更进一步：

 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

其他与当前日期、过去 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

Answer 3

将它们放在单独的表中。绝对没有理由将它们放在一个表中。

对于相对日期，我什至只是将表作为日期函数所需的参数，即

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

然后您可以知道它是-2 WEEK 或 30 DAY 差异并在您的逻辑中使用它。

如果您需要同时查看它们，您可以在查询或视图中逻辑地组合它们，而无需通过将不同的数据元素塞入同一个表来弄乱您的数据结构。

Answer 4

创建一个包含开始日期和偏移量的表。偏移的精度由您决定。

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 的内容。）