what is the best way to store time interval in a SQL server database

Question

i have a table where i want to track time so a valid entry might be:

• 1 hour 3 mins
• 47 mins
• 10 hours
• 3 mins 14 seconds

what field type is best used for this. i obviously could use varchar . .but i thought there might be something better as i would like to run queries to total the amount of time over a number of records.

Answer 1

Do not use character types to store date/time information.

In SQL Server 2008, you have the time type for this, which is what you should use if your time intervals are less than 1 day. In previous versions, or if you need to store larger intervals, you will have to use datetime or smalldatetime (depending on the precision you need).

Another option would be to choose a time unit - say, minutes - and just use an int value to represent the number of units. Just make sure that (a) the unit you choose is actually precise enough to record the smallest intervals you need to track, and (b) the actual numeric type is large enough to hold the largest intervals you need to track. A smallint might be sufficient for tracking the number of minutes within a day; on the other hand, tracking the number of milliseconds within a 10-year timeframe would have to be stored as a bigint.

Answer 2

As @Aaronaught said use a date/time or datetime (as necessary) data type to store your values; but these types only store an instance in time and not a time span or duration. You will need to use two fields to store an interval e.g. [time_span_start] and [time_span_end]. The difference between the two will give you the interval.

The longer answer to your question can be answered by downloading a copy of "Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass. It's freely available as a PDF, have a look here:

http://www.cs.arizona.edu/~rts/publications.html

Answer 3

Just use integer to store interval in seconds. DATEDIFF returns integer. Write a function that turns it into text. This one needs some adjustmens (so it shows "1 min", not "1 mins"), but should work ok:

CREATE FUNCTION dbo.SecondsToText(@seconds int)
RETURNS VARCHAR(100)
AS
BEGIN
  declare @days int;
  set @days = @seconds/(3600 * 24);
  declare @hours int;
  set @hours = (@seconds - @days * 3600 * 24) / 3600;
  declare @minutes int;
  set @minutes = (@seconds - @days * 3600 * 24 - @hours * 3600) / 60;
  set @seconds = (@seconds - @days * 3600 * 24 - @hours * 3600 - @minutes * 60);
  RETURN 
    RTRIM(CASE WHEN @days > 0 THEN CAST(@days as varchar) + ' days ' ELSE '' END +
    CASE WHEN @hours > 0 THEN CAST(@hours as varchar) + ' hours ' ELSE '' END +
    CASE WHEN @minutes > 0 THEN CAST(@minutes as varchar) + ' minutes ' ELSE '' END + 
    CASE WHEN @seconds > 0 THEN CAST(@seconds as varchar) + ' seconds ' ELSE '' END)
END
GO

Answer 4

Depends on your range of time - either convert everything to seconds and just store that value as an INT, or if your span of times is larger, you might want to use fields for hours, minutes, seconds separately.

Also, SQL Server 2008 introduces a new TIME data type which allows you to store time-only values.