提问



哪一个:



  • datetime

  • datetime2



是 推荐的在SQL Server 2008中存储日期和时间的方式?[82] [83]


我知道精度(和存储空间可能)的差异,但暂时忽略了这些,是否有关于何时使用什么的最佳实践文档,或者我们应该只使用datetime2?

最佳参考


datetime的MSDN文档建议使用datetime2。以下是他们的建议:[84] [85]



  使用timedatedatetime2
  datetimeoffset新的数据类型
  工作。这些类型与SQL一致
  标准。它们更便携。
  timedatetime2datetimeoffset
  提供更多的秒精度。
  datetimeoffset提供时区
  支持全球部署
  应用。



datetime2具有更大的日期范围,更大的默认小数精度和可选的用户指定精度。此外,根据用户指定的精度,它可能使用较少的存储空间。

其它参考1


DATETIME2的日期范围为0001/01/01至9999/12/31,而DATETIME类型仅支持年份1753-9999。


此外,如果需要,DATETIME2在时间上可以更精确; DATETIME限制在3 1/3毫秒,而DATETIME2可以精确到100ns。


两种类型都映射到.NET中的System.DateTime - 没有区别。


如果您有选择,我建议尽可能使用DATETIME2。我没有看到使用DATETIME的任何好处(向后兼容除外) - 你会遇到更少的麻烦(日期超出范围并且麻烦这样)。


另外:如果您只需要日期(没有时间部分),请使用DATE - 它与DATETIME2一样好并节省空间!:-)同样只限时间 - 使用TIME这就是这些类型的用途!

其它参考2


datetime2 在大多数方面获胜,但(旧应用兼容性)



  1. 较大的值范围

  2. 更好准确度

  3. 较小的存储空间(如果指定了可选的用户指定精度)






请注意以下几点



  • 语法


    • datetime2 [[(小数秒精度=>查看低于存储空间大小)]]


  • 精确,规模


    • 0到7位数,精度为100ns。

    • 默认精度为7位数。


  • 存储空间大小


    • 6个字节,精度小于3;

    • 7个字节,用于精度3和4.

    • 所有其他精度需要8个字节


  • DateTime2(3)与DateTime具有相同的位数,但使用7个字节的存储而不是8个字节(SQLHINTS- DateTime Vs DateTime2)

  • 在datetime2上查找更多信息(Transact-SQL MSDN文章)



图像来源:
MCTS Self-Paced Training Kit(考试70-432):Microsoft®SQLServer®2008 - 实施和维护
第3章:表格 - >第1课:创建表格 - >第66页[86] [87] [88]

其它参考3


我同意@marc_s和@Adam_Poward - DateTime2是前进的首选方法。它具有更广泛的日期,更高的精度,并使用相同或更少的存储(取决于精度)。


讨论错过了一件事,但是......

@Marc_s声明:Both types map to System.DateTime in .NET - no difference there。这是正确的,然而,反之则不正确 ...并且在进行日期范围搜索时很重要(例如找到我在2010年5月5日修改的所有记录)。


.NET的Datetime版本与DateTime2具有相似的范围和精度。当将.net Datetime向下映射到旧的SQL DateTime时,会发生隐式舍入。旧的SQL DateTime精确到3毫秒。这意味着11:59:59.997尽可能接近当天结束。任何更高的值都会向上舍入到第二天。


尝试这个 :


declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'


避免这种隐式舍入是转移到DateTime2的重要原因。隐含的日期舍入显然会引起混淆:



  • SQL Server中的奇怪日期时间行为

  • http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a

  • SQL Server 2008和毫秒

  • http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx

  • http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx


其它参考4


如果你是一个尝试将Now()写入相关字段的Access开发人员,DateTime2会造成严重破坏。刚刚进行了Access - > SQL 2008 R2迁移,它将所有日期时间字段都放在DateTime2中。使用Now()追加记录作为被轰炸的值。这是在2012年1月1日下午2:53:04没关系,但不是在2012年1月10日下午2:53:04。 [90] [92] [93]


一旦角色有所作为。希望它对某人有帮助。

其它参考5


下面是一个示例,它将向您展示smalldatetime,datetime,datetime2(0)和datetime2(7)之间存储大小(字节)和精度的差异:


DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp


返回


sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8


因此,如果我想将信息存储到第二个 - 但不是毫秒 - 如果我使用datetime2(0)而不是datetime或datetime2(7),我可以每个节省2个字节。

其它参考6


几乎所有的答案和评论都是关于优点和重点的重点。这里是对所有优点和缺点的回顾以及一些关键的缺点(下面的#2)我只见过一次或根本没有提到过。



  1. 优点:



1.1。更符合ISO标准(ISO 8601)(虽然我不知道这在实践中如何发挥作用)。


1.2。更多范围(1/1/0001至12/31/9999对比1/1/1753-12/31/9999)(尽管额外的范围,所有在1753年之前,可能不会被使用,除了ex。,在历史,天文,地质等应用程序)。


1.3。完全匹配.NET DateTime类型范围的范围(尽管如果值在目标类型的范围和精度范围内,除了Con#2.1之外,两者都来回转换没有特殊编码,否则将发生错误/舍入)。


1.4。更精确(100纳秒又名0.000,000,1秒与3.33毫秒又名0.003,33秒)(尽管除了ex。之外,在工程/科学应用程序中可能不会使用额外的精度)。


1.5。当配置为类似(如在1毫秒中不是相同(如在3.33毫秒中),如Iman Abidi声称的那样)精度为DateTime时,使用更少的空间(7对8字节) ,但当然,你会失去精确的利益,这可能是两个中的一个(另一个是范围)最受欢迎,虽然可能是不必要的好处)。



  1. CONS:



2.1。将参数传递给.NET SqlCommand时,如果您传递的值超出SQL Server DateTime的范围和/或精度,则必须指定System.Data.SqlDbType.DateTime2,因为它默认为System.Data.SqlDbType.DateTime


2.2。无法使用数值和运算符在SQL Server表达式中隐式/轻松转换为浮点数字(自最小日期时间以来的天数)值,以便对其执行以下操作:


2.2.1。添加或减去天数或部分天数。注意:当您需要考虑日期时间的多个(如果不是全部)部分时,使用DateAdd函数作为变通方法并非易事。


2.2.2。为了年龄计算,取两个日期时间之间的差异。注意:您不能简单地使用SQL Server的DateDiff函数,因为它不会像大多数人预期的那样计算age,如果两个日期时间恰好跨越日历/时钟日期 - 时间边界如果指定的单位即使是该单位的一小部分,它也会将该差值作为该单位的1与0相比返回。例如,Day中的DateDiff的两个日期 - 如果这些日期时间在不同的日历日(即1999-12-31 23:59:59.9999999和2000-01-01 00:00:00.0000000,则相隔仅1毫秒的时间将返回1对0(天) )。相同的1毫秒差异日期 - 如果移动它们不跨越日历日,将在Day的0(天)中返回DateDiff。


2.2.3。通过简单地转换为Float然后再转回DateTime来获取Avg日期时间(在聚合查询中)。


注意:要将DateTime2转换为数字,您必须执行以下公式,仍然假设您的值不小于1970年(这意味着您将失去所有额外范围再加上217年注意:您可能无法简单地调整公式以允许额外的范围,因为您可能会遇到数字溢出问题。


25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 - 来源:https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html[94]


当然,你也可以Cast DateTime首先(如果必要的话再回到DateTime2),但你会失去精确度和范围(所有在1753之前)的好处DateTime2DateTime相比,
DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp
最大,同时也是最不可能的2,这就是为什么当你失去对浮点数的隐式/简单转换时使用它的问题(加时/减法/天数)(对比DateDiff)/Avg计算效益,这是我的经验中的一个重要因素。


顺便说一句,日期时间的Avg是(或至少应该)一个重要的用例。 a)除了用于获得平均持续时间时,日期时间(因为共同的基准日期时间)用于表示持续时间(通常的做法),b)获得关于平均日期的仪表板类型统计数据也是有用的 - time位于范围/行组的日期时间列中。 c)标准(或至少应该是标准的)ad-hoc查询来监视/排除列中可能无效/不再有效和/或可能需要弃用的值列出每个值的发生次数和(如果可用)与该值相关的MinAvgMax日期时间戳。

其它参考7


使用非美国DATEFORMAT设置时,将日期字符串解释为datetimedatetime2也可能不同。例如。


set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2


这为datetime2013-06-05(即6月5日)datetime2返回2013-05-06(即5月6日)。但是,当dateformat设置为mdy时,@d@d2都返回2013-06-05


datetime行为似乎与SET DATEFORMAT的MSDN文档不一致,后者声明:某些字符串格式,例如ISO 8601,独立于DATEFORMAT设置进行解释。显然不是真的![95]


直到我被这个咬了,我总是认为只要正确处理yyyy-mm-dd日期,无论语言/区域设置如何。

其它参考8


虽然使用datetime2增加了精度,但有些客户端不支持 date , time 或 datetime2 并强制使用转换为字符串文字。特别是Microsoft提到这些数据类型的低级ODBC,OLE DB,JDBC和SqlClient问题,并有一个图表显示每个可以如何映射类型。[96] [97]]]


如果值兼容性超过精度,请使用datetime [98]

其它参考9


根据这篇文章,如果你想使用DateTime2具有相同的DateTime精度,你只需要使用DateTime2(3)。这应该给你相同的精度,占用更少的字节,并提供扩展的范围。 [99]

其它参考10


我只是偶然发现了DATETIME2的另一个优点:它避免了Python adodbapi模块中的一个错误,如果传递的标准库datetime值为非零微秒,它会爆炸。 a DATETIME列,但如果列定义为DATETIME2则可以正常工作。

其它参考11


旧问题......但是我想在这里添加一些尚未说明的东西...(注意:这是我自己的观察,所以不要求任何参考)


在过滤条件中使用时,Datetime2会更快。


TLDR:


在SQL 2016中,我有一个包含十万行的表和一个日期时间列ENTRY_TIME,因为它需要存储最多几秒的确切时间。在执行具有许多连接和子查询的复杂查询时,当我使用where子句时:


WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'


当有数百行时,查询很好,但是当行数增加时,查询开始出现此错误:


Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.


我删除了where子句,并且意外地,查询在1秒内​​运行,尽管现在获取了所有日期的所有行。我用where子句运行内部查询,花了85秒,没有where子句花了0.01秒。


我在这里遇到了很多关于这个问题的线程作为日期时间过滤性能


我优化了一下查询。但我得到的真正速度是将datetime列更改为datetime2。


现在,之前超时的同一查询只需不到一秒钟。


干杯

其它参考12


Select ValidUntil + 1
from Documents


上面的SQL无法使用DateTime2字段。
它返回并出错操作数类型冲突:datetime2与int不兼容


添加1以获得第二天是开发人员多年来一直在做的事情。现在微软有一个超级新的datetime2字段,无法处理这个简单的功能。


让我们使用比旧版本更糟糕的新类型,我不这么认为!

其它参考13


我认为DATETIME2是存储日期的更好方法,因为它的效率高于
DATETIME。在SQL Server 2008中,您可以使用DATETIME2,它存储日期和时间,需要6-8个字节进行存储,精度为100纳秒。因此,任何需要更高时间精度的人都需要DATETIME2。