Here's one more or less for perfection's sake.
这里有一个或多或少是为了完美。
Microsoft SQL Server only contains the field type datetime for storing dates and times.
Microsoft SQL Server仅包含用于存储日期和时间的字段类型datetime。
But let's say that I want to store a list of business hours, where the date is completely irrelevant. Currently I'm using the datetime type and then simply displaying the time-portion of the data. But I have two problems with this.
但假设我想存储一个工作时间列表，其中的日期完全不相关。目前我使用datetime类型，然后简单地显示数据的时间部分。但是我有两个问题。
- It seems awkwardly inefficient.
- 看起来笨拙低效的。
- It may confuse future developers to see a full-blown date coming along with the time, which they may not know whether is used anywhere or not.
- 未来的开发人员看到一个完整的日期随时间而来可能会感到困惑，他们可能不知道这个日期是否在任何地方使用。
And so it begs the question; in the absence of a specific time field (like in MySQL), what is the most optimal way of storing only a particular time of day, from 00:00 to 23:59?
这就引出了一个问题;在没有特定时间字段(如MySQL)的情况下，只存储一天中某个时间(从00:00到23:59)的最佳方式是什么?
UPDATE: It's SQL Server 2005. (Also I'd just be interested in knowing what to do in general when there is no time type.)
更新:它是SQL Server 2005。(我还想知道在没有时间类型的情况下，通常该做什么。)
5 个解决方案
#1
7
For SQL Server 2005 or older...
对于SQL Server 2005或更老的…
If you only want to know to the minute, you can store it as an int in the range of 1-1440. 1 is 00:01 and 1440 is 0:00.
如果你只想马上知道，你可以将它作为一个int值存储在1-1440的范围内。1是00:01 1440是0:00。
It would be easy do display as a time again if you like:
如果你喜欢的话，它很容易再次显示出来:
SELECT CAST((605 / 60) as varchar) + ':' + RIGHT('0' + CAST((605 % 60) as varchar), 2)
选择CAST((605 / 60)作为varchar) + ':' + RIGHT('0' + CAST(605% % 60)作为varchar)， 2)
An additional advantage of this is that if you use a smallint data type you are saving 1-3 bytes per record from the built-in TIME datatype.
这样做的另一个好处是，如果您使用smallint数据类型，您将从内置的时间数据类型中为每个记录节省1-3字节。
TIME uses 3-5 bytes per row and smallint is 2 bytes per row.
时间每一行使用3-5字节，而smallint每一行是2字节。
The extra bytes are for seconds and fractional seconds I believe.
额外的字节是秒和小数秒。
EDIT
编辑
It's more complicated with seconds but still doable I should think...
这比几秒钟要复杂得多，但我还是认为……
1-86400 range (seconds per day)
1-86400范围(每天秒)
DECLARE @i INT
SET @i = 3661
SELECT RIGHT('0' + CAST((@i / 3600) as varchar),2) --hours
+ ':' + RIGHT('0' + CAST((@i % 3600)/60 as varchar), 2) -- minutes
+ ':' + RIGHT('0' + CAST((@i % 3600)%60 as varchar), 2) -- seconds
#2
5
SQL Server 2008 has a TIME datatype:
SQL Server 2008有一个时间数据类型:
http://www.sql-server-performance.com/2007/datetime-2008/
http://www.sql-server-performance.com/2007/datetime-2008/
DECLARE @dt as TIME
SET @dt = getdate()
PRINT @dt
Upgrade to SQL 2008?
升级到SQL 2008吗?
#4
2
Personally I would not consider the points raised as sufficient enough to move away from using DATETIME or SMALLDATETIME.
就我个人而言，我不认为提出的要点足以使我远离使用DATETIME或SMALLDATETIME。
- An INT uses 4 bytes, as does a SMALLDATETIME
- INT使用4个字节，SMALLDATETIME也使用4个字节
- People make mistakes with SMALLINT that cause implicit type conversions (increasing cpu load)
- 使用SMALLINT会导致隐式类型转换(增加cpu负载)
- Disk Space is cheap, you need a lot of bytes to add up to anythign significant
- 磁盘空间很便宜，您需要大量的字节来增加任何重要的设计
- Code such as
WHERE minutes < 720is less understandable thanWHERE time < '12:00' - 代码，如分钟< 720，比时间< 12:00'更不容易理解。
- Display issues (such as conversion of DATETIME to hh:mm) is often best place in the client
- 显示问题(例如将DATETIME转换为hh:mm)通常是客户机中最好的位置
- Using DATETIME allows future flexibility, such moving to seconds instead of minutes
- 使用DATETIME可以实现未来的灵活性，比如移动到秒而不是分钟
That said, I have used INTEGER fields to hold number of seconds, such as when they're predominantly used for calculating average durations, etc.
也就是说，我使用了整数字段来保存秒数，比如当它们主要用于计算平均持续时间时，等等。
My single biggest consideration in choosing the type is how the value will be used; to ensure legible code and performant execution plans.
在选择类型时，我最大的考虑是如何使用值;确保清晰的代码和执行计划的执行。
#5
0
SQL 2008 fixed this problem as others have noted but in 2005:
SQL 2008解决了这个问题，正如其他人注意到的，但是在2005年:
Do you need to perform any date math on the times? If not, you can store it as a string.
你需要在纽约时报上做一些日期计算吗?如果不是，可以将其存储为字符串。
If you need to perform date math, a datetime with the day set to zero along with a descriptive column name shouldn't stump any future devs (and thanks for keeping us in mind).
如果您需要执行日期计算，那么日期设置为零的datetime和描述性列名不应该影响任何未来的开发人员(感谢您让我们记住这一点)。
And yes, datetime is clunky for time only storage but it functions just fine.
是的，datetime用于存储时间，但它的功能很好。
#1
7
For SQL Server 2005 or older...
对于SQL Server 2005或更老的…
If you only want to know to the minute, you can store it as an int in the range of 1-1440. 1 is 00:01 and 1440 is 0:00.
如果你只想马上知道，你可以将它作为一个int值存储在1-1440的范围内。1是00:01 1440是0:00。
It would be easy do display as a time again if you like:
如果你喜欢的话，它很容易再次显示出来:
SELECT CAST((605 / 60) as varchar) + ':' + RIGHT('0' + CAST((605 % 60) as varchar), 2)
选择CAST((605 / 60)作为varchar) + ':' + RIGHT('0' + CAST(605% % 60)作为varchar)， 2)
An additional advantage of this is that if you use a smallint data type you are saving 1-3 bytes per record from the built-in TIME datatype.
这样做的另一个好处是，如果您使用smallint数据类型，您将从内置的时间数据类型中为每个记录节省1-3字节。
TIME uses 3-5 bytes per row and smallint is 2 bytes per row.
时间每一行使用3-5字节，而smallint每一行是2字节。
The extra bytes are for seconds and fractional seconds I believe.
额外的字节是秒和小数秒。
EDIT
编辑
It's more complicated with seconds but still doable I should think...
这比几秒钟要复杂得多，但我还是认为……
1-86400 range (seconds per day)
1-86400范围(每天秒)
DECLARE @i INT
SET @i = 3661
SELECT RIGHT('0' + CAST((@i / 3600) as varchar),2) --hours
+ ':' + RIGHT('0' + CAST((@i % 3600)/60 as varchar), 2) -- minutes
+ ':' + RIGHT('0' + CAST((@i % 3600)%60 as varchar), 2) -- seconds
#2
5
SQL Server 2008 has a TIME datatype:
SQL Server 2008有一个时间数据类型:
http://www.sql-server-performance.com/2007/datetime-2008/
http://www.sql-server-performance.com/2007/datetime-2008/
DECLARE @dt as TIME
SET @dt = getdate()
PRINT @dt
Upgrade to SQL 2008?
升级到SQL 2008吗?
#3
#4
2
Personally I would not consider the points raised as sufficient enough to move away from using DATETIME or SMALLDATETIME.
就我个人而言，我不认为提出的要点足以使我远离使用DATETIME或SMALLDATETIME。
- An INT uses 4 bytes, as does a SMALLDATETIME
- INT使用4个字节，SMALLDATETIME也使用4个字节
- People make mistakes with SMALLINT that cause implicit type conversions (increasing cpu load)
- 使用SMALLINT会导致隐式类型转换(增加cpu负载)
- Disk Space is cheap, you need a lot of bytes to add up to anythign significant
- 磁盘空间很便宜，您需要大量的字节来增加任何重要的设计
- Code such as
WHERE minutes < 720is less understandable thanWHERE time < '12:00' - 代码，如分钟< 720，比时间< 12:00'更不容易理解。
- Display issues (such as conversion of DATETIME to hh:mm) is often best place in the client
- 显示问题(例如将DATETIME转换为hh:mm)通常是客户机中最好的位置
- Using DATETIME allows future flexibility, such moving to seconds instead of minutes
- 使用DATETIME可以实现未来的灵活性，比如移动到秒而不是分钟
That said, I have used INTEGER fields to hold number of seconds, such as when they're predominantly used for calculating average durations, etc.
也就是说，我使用了整数字段来保存秒数，比如当它们主要用于计算平均持续时间时，等等。
My single biggest consideration in choosing the type is how the value will be used; to ensure legible code and performant execution plans.
在选择类型时，我最大的考虑是如何使用值;确保清晰的代码和执行计划的执行。
#5
0
SQL 2008 fixed this problem as others have noted but in 2005:
SQL 2008解决了这个问题，正如其他人注意到的，但是在2005年:
Do you need to perform any date math on the times? If not, you can store it as a string.
你需要在纽约时报上做一些日期计算吗?如果不是，可以将其存储为字符串。
If you need to perform date math, a datetime with the day set to zero along with a descriptive column name shouldn't stump any future devs (and thanks for keeping us in mind).
如果您需要执行日期计算，那么日期设置为零的datetime和描述性列名不应该影响任何未来的开发人员(感谢您让我们记住这一点)。
And yes, datetime is clunky for time only storage but it functions just fine.
是的，datetime用于存储时间，但它的功能很好。