How would you store a time or time range in SQL? It won't be a datetime because it will just be let's say 4:30PM (not, January 3rd, 4:30pm). Those would be weekly, or daily meetings. The type of queries that I need are of course be for display, but also later will include complex queries such as avoiding conflicts in schedule. I'd rather pick the best datatype for that now.
如何在SQL中存储时间或时间范围？它不会是一个日期时间，因为它只是让我们说下午4:30（不是1月3日下午4:30）。那些是每周或每日会议。我需要的查询类型当然是用于显示，但稍后还将包括复杂查询，例如避免计划中的冲突。我现在宁愿选择最好的数据类型。
I'm using MS SQL Server Express 2005.
我正在使用MS SQL Server Express 2005。
Thanks!
谢谢！
Nathan
弥敦道
5 个解决方案
#1
6
Personally I would find this a reason to upgrade to 2008 which has a separate time datatype.
就个人而言，我觉得这是升级到2008年的一个原因，它有一个单独的时间数据类型。
#2
4
I would recommend still using a DateTime data type and ignoring the date values--ideally using the static MinDate for SQL (Google it). This will give you the benefits of working with a strongly typed field and the only cost will be a few extra bytes.
我建议仍然使用DateTime数据类型并忽略日期值 - 理想情况下使用静态MinDate for SQL（Google it）。这将为您提供使用强类型字段的好处，唯一的成本将是几个额外的字节。
As for ranges, store them in two separate columns. Then you can subtract one from the other to determine the difference.
至于范围，将它们存储在两个单独的列中。然后你可以从另一个中减去一个来确定差异。
Edit: did some Googling.
编辑：做了一些谷歌搜索。
- SQL Server 2008 adds a Time data type, so you might want to consider that.
- SQL Server 2008添加了Time数据类型，因此您可能需要考虑这一点。
- You can use SQL 2005's DateTime type and combine it with the CONVERT function to extract just the HH:MM:SS.MMM
- SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS(24H)] (Found on this handy-dandy page)
- SELECT CONVERT（VARCHAR（12），GETDATE（），114）AS [HH：MI：SS（24H）]（在这个方便的花花公子页面上找到）
- 您可以使用SQL 2005的DateTime类型并将其与CONVERT函数组合以仅提取HH：MM：SS.MMM SELECT CONVERT（VARCHAR（12），GETDATE（），114）AS [HH：MI：SS（24H）] （在这个方便的花花公子页面上找到）
- Different SQL versions support different minimum dates. You could use a static date that will be supported by all such as 1/1/2000, or you could use SQL 2005's minimum value of 1/1/1753 and append the time values to that startic day
- 不同的SQL版本支持不同的最小日期。您可以使用所有人都支持的静态日期，例如1/1/2000，或者您可以使用SQL 2005的最小值1/1/1753并将时间值附加到该开始日期
So if you stick with 2005, pick your static date, like 1/1/2000, and store your times on it. So 1m:30s would be 2000-1-1 00:01:30.000, and 1h:15m would be 2000-1-1 01:15:00.000
因此，如果您坚持使用2005年，请选择静态日期，例如1/1/2000，并将时间存储在其上。所以1m：30s将是2000-1-1 00：01：30.000,1h：15m将是2000-1-1 01：15：00.000
You can then do Date2 - Date1 and get your result of (1h:15:m - 1m:30s) 2000-01-01 01:13:45.000. CONVERT it and you'll have 1:13:45.
然后你可以做Date2 - Date1并获得你的结果（1h：15：m - 1m：30s）2000-01-01 01：13：45.000。转换它，你将有1:13:45。
#3
1
You could store it as an int as 24 hour time and format as needed.
您可以将其作为int存储为24小时时间并根据需要进行格式化。
Or store it as a datetime with some fixed date and remove it as needed for display: Jan 1 2000 4:30PM
或者将其存储为具有固定日期的日期时间，并根据需要将其删除以显示：2000年1月1日下午4:30
I would go with datetime field as it gives you the power of all the datetime related functionality.
我会使用datetime字段，因为它为您提供了所有日期时间相关功能的强大功能。
#4
1
You might want to consider storing it as an int column representing the number of minutes since midnight. In your entity you could expose this as a TimeSpan (or int) representing the same thing. You'd only need to convert between your display values (time format) and the database value (minutes) in order to perform your queries and this could easily be done in your entity (TimeSpan.TotalMinutes, for example).
您可能需要考虑将其存储为表示自午夜以来的分钟数的int列。在您的实体中，您可以将其公开为表示相同内容的TimeSpan（或int）。您只需要在显示值（时间格式）和数据库值（分钟）之间进行转换，以便执行查询，这可以在您的实体中轻松完成（例如，TimeSpan.TotalMinutes）。
#5
0
to me it sounds like you're developing a type of meeting scheduler or something to display the meetings.
对我而言，这听起来像是在开发一种会议日程安排程序或者某种东西来显示会议。
i think that i would set it p with 2 columns MeetingStart and MeetingEnd, both as datetime fields. This way, you can determine the length of the meeting, and since you already have the date you can easily use it to display it on a calendar or something.
我认为我会将它设置为2列MeetingStart和MeetingEnd，两者都作为datetime字段。这样，您就可以确定会议的长度，并且由于您已经拥有日期，因此可以轻松地将其用于在日历或其他内容上显示。
#1
6
Personally I would find this a reason to upgrade to 2008 which has a separate time datatype.
就个人而言，我觉得这是升级到2008年的一个原因，它有一个单独的时间数据类型。
#2
4
I would recommend still using a DateTime data type and ignoring the date values--ideally using the static MinDate for SQL (Google it). This will give you the benefits of working with a strongly typed field and the only cost will be a few extra bytes.
我建议仍然使用DateTime数据类型并忽略日期值 - 理想情况下使用静态MinDate for SQL（Google it）。这将为您提供使用强类型字段的好处，唯一的成本将是几个额外的字节。
As for ranges, store them in two separate columns. Then you can subtract one from the other to determine the difference.
至于范围，将它们存储在两个单独的列中。然后你可以从另一个中减去一个来确定差异。
Edit: did some Googling.
编辑：做了一些谷歌搜索。
- SQL Server 2008 adds a Time data type, so you might want to consider that.
- SQL Server 2008添加了Time数据类型，因此您可能需要考虑这一点。
- You can use SQL 2005's DateTime type and combine it with the CONVERT function to extract just the HH:MM:SS.MMM
- SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS(24H)] (Found on this handy-dandy page)
- SELECT CONVERT（VARCHAR（12），GETDATE（），114）AS [HH：MI：SS（24H）]（在这个方便的花花公子页面上找到）
- 您可以使用SQL 2005的DateTime类型并将其与CONVERT函数组合以仅提取HH：MM：SS.MMM SELECT CONVERT（VARCHAR（12），GETDATE（），114）AS [HH：MI：SS（24H）] （在这个方便的花花公子页面上找到）
- Different SQL versions support different minimum dates. You could use a static date that will be supported by all such as 1/1/2000, or you could use SQL 2005's minimum value of 1/1/1753 and append the time values to that startic day
- 不同的SQL版本支持不同的最小日期。您可以使用所有人都支持的静态日期，例如1/1/2000，或者您可以使用SQL 2005的最小值1/1/1753并将时间值附加到该开始日期
So if you stick with 2005, pick your static date, like 1/1/2000, and store your times on it. So 1m:30s would be 2000-1-1 00:01:30.000, and 1h:15m would be 2000-1-1 01:15:00.000
因此，如果您坚持使用2005年，请选择静态日期，例如1/1/2000，并将时间存储在其上。所以1m：30s将是2000-1-1 00：01：30.000,1h：15m将是2000-1-1 01：15：00.000
You can then do Date2 - Date1 and get your result of (1h:15:m - 1m:30s) 2000-01-01 01:13:45.000. CONVERT it and you'll have 1:13:45.
然后你可以做Date2 - Date1并获得你的结果（1h：15：m - 1m：30s）2000-01-01 01：13：45.000。转换它，你将有1:13:45。
#3
1
You could store it as an int as 24 hour time and format as needed.
您可以将其作为int存储为24小时时间并根据需要进行格式化。
Or store it as a datetime with some fixed date and remove it as needed for display: Jan 1 2000 4:30PM
或者将其存储为具有固定日期的日期时间，并根据需要将其删除以显示：2000年1月1日下午4:30
I would go with datetime field as it gives you the power of all the datetime related functionality.
我会使用datetime字段，因为它为您提供了所有日期时间相关功能的强大功能。
#4
1
You might want to consider storing it as an int column representing the number of minutes since midnight. In your entity you could expose this as a TimeSpan (or int) representing the same thing. You'd only need to convert between your display values (time format) and the database value (minutes) in order to perform your queries and this could easily be done in your entity (TimeSpan.TotalMinutes, for example).
您可能需要考虑将其存储为表示自午夜以来的分钟数的int列。在您的实体中，您可以将其公开为表示相同内容的TimeSpan（或int）。您只需要在显示值（时间格式）和数据库值（分钟）之间进行转换，以便执行查询，这可以在您的实体中轻松完成（例如，TimeSpan.TotalMinutes）。
#5
0
to me it sounds like you're developing a type of meeting scheduler or something to display the meetings.
对我而言，这听起来像是在开发一种会议日程安排程序或者某种东西来显示会议。
i think that i would set it p with 2 columns MeetingStart and MeetingEnd, both as datetime fields. This way, you can determine the length of the meeting, and since you already have the date you can easily use it to display it on a calendar or something.
我认为我会将它设置为2列MeetingStart和MeetingEnd，两者都作为datetime字段。这样，您就可以确定会议的长度，并且由于您已经拥有日期，因此可以轻松地将其用于在日历或其他内容上显示。