Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.