在SQL server数据库中存储时间间隔的最佳方式是什么

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

我有一个表，我想跟踪时间，所以一个有效的条目可能是:

1 hour 3 mins
1小时3分钟
47 mins
47分钟
10 hours
10个小时
3 mins 14 seconds
3分钟14秒

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.

最好使用什么字段类型。我显然可以使用varchar . . .但是我认为可能有更好的方法，因为我想运行查询以在多个记录中总计时间。

5 个解决方案

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

在SQL Server 2008中，您有时间类型，如果您的时间间隔小于1天，那么应该使用这种类型。在以前的版本中，或者如果您需要存储较大的间隔，您将不得不使用datetime或smalldatetime(取决于您需要的精度)。

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.

另一种选择是选择一个时间单位——比如分钟——然后使用int值来表示单位的数量。只要确保(a)你选择的单元足够精确，可以记录你需要跟踪的最小的间隔，(b)实际的数值类型足够大，可以容纳你需要跟踪的最大的间隔。一个小的int数可能足以追踪一天内的分钟数;另一方面，跟踪10年时间内的毫秒数，则必须存储为bigint。

#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:

只用整数来存储秒的间隔。DATEDIFF返回整数。编写一个将其转换为文本的函数。这个需要一些调整(所以显示“1分钟”，而不是“1分钟”)，但是应该可以:

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

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

取决于您的时间范围——要么将所有内容转换为秒，并将该值存储为INT，要么如果时间跨度较大，您可能希望将字段分别用于小时、分钟、秒。

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

此外，SQL Server 2008引入了一种新的时间数据类型，允许您存储只有时间的值。

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

如@Aaronaught所说，使用日期/时间或日期时间(必要时)数据类型来存储值;但是这些类型只在时间中存储实例，而不存储时间跨度或持续时间。您将需要使用两个字段来存储间隔，例如[time_span_start]和[time_span_end]。两者之间的差异会给出区间。

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:

你的问题可以通过下载Richard T. Snodgrass的《用SQL开发面向时间的数据库应用程序》来回答。它是免费提供的PDF，看这里:

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

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

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

与Tony的答案相关，您还可以使用一个datetime列，相对于一个标准的开始时间，这个时间列对所有间隔都是隐式的——例如:1/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.

现在做行和显然不容易，所以可以考虑添加DATEDIFF(s， '1/1/1900'， interval)的持久化计算列，以提供执行和的秒数。

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

下面是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.

因为SQL Server实现转换的数字和日期,0 - > 1/1/1900 12:00点,0.5 - > 1/1/1900 12:00点,1 - > 1/2/1900上午12:00等即整数被视为1/1/1900以来的天数,小数部分的分数在一天之内。你可以很天真地把这些加起来得到一个区间。

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

按预期提供“1900-01-04 12:00 - 0.000”。

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.

我并不提倡在一般情况下这样做，YMMV，并且您选择的任何设计解决方案都应该根据您的所有需求进行验证。

#1