CODEWITHSUNDEEP
sql-serverruntime
leora
leora

Reputation: 196539

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

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

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.

Upvotes: 16

Views: 22951

Answers (5)

Tony
Tony

Reputation: 10327

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.

A very detailed answer to your question can be obtained 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

Upvotes: 5

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 0

LukLed
LukLed

Reputation: 31842

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

Upvotes: 5

marc_s
marc_s

Reputation: 754538

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.

Upvotes: 2

Aaronaught
Aaronaught

Reputation: 122654

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.

Upvotes: 16

Related Questions