将日期存储为整数（数字），有哪些优势？

Question

问题 1

我正在使用一个将日期存储为整数（实际上是numeric(8,0)）的系统，并且我注意到其他系统也将日期存储为整数，例如此帖子中的思科。示例

20120101  -- 01 Jan 2012

保留数字日期系统而不使用SQL Datetime有什么优势吗？

问题2

现在我正在尝试循环遍历数字日期以查找两个日期之间的客户。如果start和enddate跨越两个月，我会得到成千上万条记录，而不仅仅是60条。例如：

create table #temp1(day int,capacity int) /* just a temp table */

declare @start int 
declare @end int

set @start=20111201
set @end = 20120131

while (@start <= @end) 
Begin
    insert into #temp1  /* I am storing things in #temp table so data looks pretty */
    exec usp_GetDailyCap @date1= @start

    set @start = @start + 1;    
end

select * from #temp1

这个逻辑会拉取8931条记录，而不是60条。有没有更好的方法来改进上述逻辑，以便只拉取有效的日期？我尝试了IsDate和子查询，但效率不高。

- TheTechGuy

---

如果您正在使用SQL Server 2008或更高版本，实际上您可以直接使用日期数据类型。它的大小稍小，并且不强制您包含时间，但几乎所有SQL的日期时间函数仍然适用于它。 - DForck42

我只看到这种方法的缺点，没有任何优势。 - user1822

Answer 1

回答你的第一个问题，我建议在SQL Server中使用DATETIME数据类型。不仅仅是出于性能原因，还可以利用特定于关系数据库管理系统的功能。例如，如果要进行基本的日期计算（比如DATEDIFF()、DATEADD()、DATEPART()等函数），你将不得不重新设计很多逻辑。这些函数显然是针对DATETIME数据类型进行优化的，使用起来非常方便。 至于你的第二个问题，你遇到的正是第一个问题（以及我的答案）所针对的确切问题。你将20111201和20120131视为日期，并且你的大脑告诉你它们之间应该相差60天。然而，你的循环是基于这个差值进行的... 20120131 - 20111201 = 8930（包括循环的话就是8931） 换句话说，你的WHILE循环执行了8931次。这是因为这些都是整数值，你的循环无法直接从20111231跳到20120101。 你整数不会考虑年份和月份的限制（即你的问题2）。

- Thomas Stringer

---

那正是我的问题。对于数字日期，循环可以达到数千天，而不仅仅是30天或29天。但请记住，我正在使用一个专业系统。而且看起来连思科也在使用它。 - TheTechGuy

除了性能和功能之外，还有完整性。使用整数作为日期，数据库将允许20121301、20120230甚至20129999作为日期。 - ypercubeᵀᴹ

@Jackofall Cisco在其背后没有关系型数据库管理系统（RDBMS）的平台。他们自己编写了逻辑。为什么他们不只是使用整数呢？从底层软件来看，这可能是最简单的方式。但我们现在谈论的是完全不同的事情。 - Thomas Stringer

@Shark，我正在处理的软件叫做Nexant。这个数据库是非常专业地编写的（没有新手在这里），有关系、外键、大量的表格，字段名称的正确编码。但我理解你的观点，并且同意你的看法，至少可以说这样会损失数据完整性。不过，为什么一个专业的系统会使用数字日期呢？ - TheTechGuy

@Jackofall: 存储日期作为整数（并存在间隔）与存储日期时间戳作为整数之间存在着很大的区别 - 甚至存储日期作为整数（就像VB/Excel那样）也是如此。 - ypercubeᵀᴹ

你提供的链接表明Cisco正在存储Unix时间戳（或某个变体）。 - ypercubeᵀᴹ

有很多（如果不是大部分）专业设计的数据库都使用了糟糕的技术。我曾经使用过许多COTS产品，但从数据库的角度来看，没有见过任何一个设计得好的。 - HLGEM

@HLGEM - 这通常是由于遗留问题和在您选择的年代中设计的系统重构困难所致，当时关系型数据库管理系统还不像现在这样先进！ - Vérace

Answer 2

1. Ralph Kimball建议将日期存储为整数。他在网上和书籍中都写了很多相关内容。 2. 您可以使用一个日历表，并为您的日期分配连续的数字，如下所示： 日期 编号 20120229 1234 20120301 1235 日历表需要生成，但这是一项非常简单的任务。

- A-K

---

我想看一下你过滤查询的案例，通过与日期表连接，并且日期以数字形式存储，过滤这些数字日期会比使用"where [date] between @startdate and @enddate"更好。 - DForck42

@DForck42，你提出的情况并不需要。"where [dateAsInt] between 20120229 and 20120329" 和 "where [date] between '20120229' and '20120329'" 将返回完全相同的行。 - A-K

他的理由是什么？ - HLGEM

Answer 3

潜在的数据类型及其大小/限制： - Decimal(8,0)：5字节 - 日期：3字节，0001-01-01到9999-12-31 - Int：4字节 数字数据类型的优点： - 它们看起来漂亮？ 数字数据类型的缺点： - 需要自定义代码来处理日期操作 - 需要自定义代码来管理正确的日期（即不允许20120230 [2012年2月30日]） - 与日期数据类型相比，占用更大的数据空间。 老实说，在我看来，你最好使用日期数据类型。

- DForck42