Storing date increments in tables

Question

I'm working on a ASP/MS SQL project that performs a lot of calculations based on dates and time. In one case, the system will lookup a value based on the nearest half hour increment throughout the day.

When designing the database should I create a lookup table with just the increments, for example: 00:00:00, 00:30:00, 01:00:00, and then create another table with the value and a FK to the hour increments? Or should I include the time increments in another column with the values? Or should I not include the increments and build the business logic within a function or stored procedure?

Answer 1

You can create a time table which stores time in minutes. Then have a column as the foreign key to the record which has got half an hour difference to it. If you need to get half an hour before a time slot and half an hour after it, you will have 2 columns. One referring to the record which has got the half an hour prior value and another has got the half an hour after value. In means the foreign key referenced to the table itself. The table definition would be something like below:

Create Table TimeTbl

(

Id int not null primary key,

TimeValue time not null,

HalfHrBeforeId int null,

HalfHrAfterId int null

)

HalfHrAfter Foreign Key referenced TimeTbl(Id)

HalfHrBeforeId Foreign Key referenced TimeTbl(Id)

In this table you will have:

1, 00:00:00, null, 30

2, 00:01:00, null, 31

...

30, 00:30:00, 1, 60

31, 00:31:00, 2, 61

...

60,01:00:00, 30, 90

...

So if you query this table each includes all time records of a day in minute, you will have foreign key to half an hour before and after and can get and compare their values, to choose the minimum one(the closest half an hour increment).

Answer 2

Assuming you have some kind of transaction table containing events that occur at various times throughout the day, then the best thing is to start by recording these events with the full date and time to whatever precision you like in one table.

To group these events into time periods for reporting, you can create a reporting period table. This table should have a start time (of day) and an end time (of day). Then you can join your event records to your reporting period records using something like...

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

One of the advantages of this approach is that you don't need to maintain foreign keys between your event data and your reporting period data. That's really convenient if you decide to change your reporting periods.

Answer 3

You could create a time table with the time data type as the primary key and create a record for each time, if the lowest level is minutes there would be 1440 records, along with an additional column to indicate the nearest half hour for each time.

It depends on what your application is going to do on whether there is a need to do this in SQL or within the code behind.