将日期增量存储在表中

Question

我正在处理一个ASP/MS项目，该项目根据日期和时间执行大量计算。在一种情况下，系统将根据一天中最接近的半小时增量来查找值。

在设计数据库时，是否应该创建一个仅包含增量的查找表，例如: 00:00:00、00:30:00、01:00:00，然后创建另一个具有值和FK到小时增量的表？还是应该在另一列中包含带有值的时间增量？或者我不应该在函数或存储过程中包括增量和构建业务逻辑？

Answer 1

您可以创建一个以分钟为单位存储时间的时刻表。然后有一个列作为记录的外键，它有半个小时的差异。如果您需要在一个时隙前半小时，在它之后半小时，您将有2列。一种是指先值半小时的记录，另一种是值后半小时的记录。表示引用到表本身的外键。表的定义如下所示：

创建表TimeTbl

(

Id int not null primary key,

TimeValue time not null,

HalfHrBeforeId int null,

HalfHrAfterId int null

)

HalfHrAfter外键引用的TimeTbl(Id)

HalfHrBeforeId外键引用的TimeTbl(Id)

在本表中，您将得到：

1，00:00:00，null，30

2，00:01:00，空，31

..。

30，00:30:30，1，60

31，00:31:00，2，61

..。

60,01:00:00，30，90

..。

因此，如果您查询此表，每个表都包含一天中每分钟的所有时间记录，那么您将在半小时之前和之后拥有外键，并且可以获取和比较它们的值，以选择最小值(最接近的半小时增量)。

Answer 2

假设您有某种事务表，其中包含在一天中不同时间发生的事件，那么最好的方法是首先将这些事件以完整的日期和时间记录到一个表中您喜欢的任何精度。

若要将这些事件分组为用于报告的时间段，可以创建报告期间表。这个表应该有一个开始时间(一天)和一个结束时间(一天)。然后你可以将你的事件记录加入到你的报告期记录中，比如.

SELECT p.start_time, count(e.event_id)
FROM event e 
  INNER JOIN reporting_period p
    ON  CONVERT(TIME, e.event_date) >= p.start_time
    and CONVERT(TIME, e.event_date) < p.end_time

这种方法的优点之一是您不需要在事件数据和报告期间数据之间维护外键。如果您决定更改报告周期，这是非常方便的。

Answer 3

您可以创建一个以时间数据类型作为主键的时刻表，并为每次创建一个记录，如果最低级别为分钟，则将有1440条记录，同时还可以添加一列，以指示每次最接近的半小时。

这取决于应用程序将做什么，取决于在SQL中还是在后面的代码中是否需要这样做。