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

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 3

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.

Answer 4

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 5

Related to Tony's answer, you can also use a single datetime column relative to a standard start time which is implicit for all intervals - for instance: 1/1/1900 12:00 AM.

In this case it is easy enough for storage:

INSERT INTO tbl (interval) VALUES (DATEADD(s, '1/1/1900', DATEDIFF(s, @starttime, @endtime))

Now this is not obviously easy for doing SUMs of rows, so you could think about adding persisted computed column(s) of DATEDIFF(s, '1/1/1900', interval) to provide seconds to perform SUMs.

Now, here's where it gets interesting for SQL Server:

Because of SQL Server's implementation for converting numbers to and from dates, 0 -> 1/1/1900 12:00 AM, 0.5 -> 1/1/1900 12:00 PM, 1 -> 1/2/1900 12:00 AM etc. i.e. the whole number is treated as the number of days since 1/1/1900 and the fractional part is the fraction within the day. So you CAN actually naively add these to get an interval.

And in fact:

SELECT  CONVERT(DATETIME, 1) + CONVERT(DATETIME, 0) + CONVERT(DATETIME, 2) + CONVERT(DATETIME, 0.5)

gives '1900-01-04 12:00:00.000' as expected

So you can do this (going around SUM by converting):

DECLARE @datetest TABLE ( dt DATETIME NOT NULL )

INSERT  INTO @datetest ( dt )
VALUES  ( 0 )
INSERT  INTO @datetest ( dt )
VALUES  ( 1 )
INSERT  INTO @datetest ( dt )
VALUES  ( 2 )
INSERT  INTO @datetest ( dt )
VALUES  ( 0.5 )

SELECT  *
FROM    @datetest

SELECT  CONVERT(DATETIME, SUM(CONVERT(FLOAT, dt)))
FROM    @datetest

I'm not advocating doing this in general, YMMV, and any design solution you choose should be verified against all your requirements.