SQL Server的日期和时间类型

时间：2023-03-08 22:13:02
SQL Server的日期和时间类型

在Microsoft SQL Server的类型系统中，使用 date 表示日期类型，使用time表示时间类型，使用DateTime和DateTime2表示日期和时间的组合，DateTime2是DateTime的升级版本，这些数据类型占用的存储空间各不相同；当存储大量数据时，合理的选择小数秒的精度，能够节省数据的占用空间。

当表示国际时间时，存在本地时间和UTC时间之别，同一个时刻，UTC时间是固定的，而本地时间由于时区的差异而不相同。如果一个数据实体的时间字段对时区敏感，那么可以使用DateTimeOffset数据类型，该类型不仅包含本地的日期和时间，还包括本地的时区，用户能够根据本地的时间和时区推算出UTC时间，公式是：UTC时间=本地时间+时区偏移。

小数秒的精度（fractional seconds precision）是指使用多少位小数表示一秒，DateTime2、Time和DataTimeOffset可以控制小数秒的精度，语法是DateTime2(n)、time(n)，DataTimeOffset(n)，n是小数秒的精度，n的取值范围是0-7，默认值是7，即使用7位小数表示1s，能够表示的最小精确时间（Accuracy）是100ns。DateTime数据类型的最小精确时间是3.33毫秒（0.00333秒），其精确度不高，建议在产品环境中，使用DateTime2(n)来代替DateTime类型。

一，日期和时间类型

DateTime2(n)表示日期和时间，Date只表示日期，Time(n)只表示时间，最后简单介绍DateTime类型。

1，DateTime2数据类型

DateTime2(n)数据类型存储日期和时间，它是DateTime的升级版本，由于小数秒n的精度可以自主设置，其存储大小（Storage Size）不固定，DateTime2(n)占用的存储空间和小数秒的精度之间的关系是：

  • DateTime2(n)内部存储的第一个字节存储精度n，后续的字节用于存储值。
  • 当小数秒的精度 n < 3 时，存储空间是1B（精度）+ B（数据）；
  • 当小数秒的精度 n 是 3 - 4 时，存储空间是1B（精度）+ 7B（数据）；
  • 当小数秒的精度 n 是 5 - 7 时，存储空间是1B（精度）+ 8B（数据），最大的小数秒精度是7，默认值是7；

DateTime2可以表示比DateTime更精确的时间，默认的数据格式是yyyy-MM-dd hh:mm:ss.nnnnnnn，DateTime2 秒默认的精度是7，即用7位小数表示一秒的精度。

datetime2 [ (fractional seconds precision) ]

下面两种声明DateTime2变量的方式是等价的：

declare @dt2 datetime2(7)

declare @dt2 datetime2

为DateTime2类型的变量赋值，需要使用SysDateTime()和SysUTCDateTime()，这两个函数返回值的类型是DateTime2(7)。

declare @dt2 datetime2

set @dt2=SYSDATETIME()

3，Date数据类型

Date数据类型只存储日期，不存储时间，仅需要3B的存储空间，默认的数据格式是yyyy-MM-dd，支持的日期范围从0001-01-01到9999-12-31

可以使用日期字符串，getdate()等函数为Date类型的变量赋值：

declare @d date

set @d='2015-07-02'

set @d=getdate()

set @d=SYSDATETIME()

4，Time类型

Time(n)数据类型只存储时间，不存储日期，需要5B的存储空间。Time(n)小数秒精度n的默认值是7，默认的数据格式是hh:mm:ss.nnnnnnn：

time [ (fractional second precision) ]

推荐使用时间字符串和sysdatetime()函数为Time类型的变量赋值，不推荐使用GetDate()函数，GetDate()函数返回的是DateTime类型，时间部分的小数位精度是3，如果对时间的精度要求高，请使用时间字符串和sysdatetime()函数为Time类型的变量赋值。

--declare @t time(7)

DECLARE @t time

set @t='13:48:43.2840467'

set @t=SYSDATETIME()

--not recommend

set @t=GETDATE()

4，DateTime数据类型

DateTime数据类型存储日期和时间，其存储空间是固定的8个字节，默认的数据格式是yyyy-MM-dd hh:mm:ss.nnn，表示从1753年1月1日到9999年12月31日的日期和时间数据，精确度为3.33毫秒（0.00333秒），也就是说，DateTime表示的日期范围从公元1753年1月1日00:00:00.000 到9999年12月31日23:59:59.997 ，精确到3.33毫秒。

在DateTime使用的8个字节中，实际上，SQL Server用两个4 字节的整数内部存储 datetime 数据类型的值。第一个 4 字节存储基准日期（即 1900 年 1 月 1 日）之前或之后的天数。基准日期是系统参考日期，不允许出现早于 1753 年 1 月 1 日的 date 值。当第一个4 字节为0 时，表示的日期是1900 年1 月1 日；在基准日期之前的日期，第一个4字节的值是负数，在基准日期之后的日期，第一个4字节的值是正数。另外一个 4 字节存储以10/3 毫秒数所代表的每天的时间。

declare @dt datetime

使用GetDate()和GetUTCDate()为DateTime类型的变量赋值，这两个函数返回值的类型是DateTime

declare @dt datetime

set @dt=getdate()

鉴于DateTime的精确度（精确度为3.3毫秒）没有DateTime2(n)高，并且占用的存储空间也比DateTime2(n)高，所以，建议在产品环境中使用DateTime2(n)来代替DateTime类型。

5，SamllDateTime 数据类型

SamllDateTime数据类型的存储空间是固定的4bytes，由Date 和时间构成，Date的取值范围从 1900-01-01 到 2079-06-06，时间部分是24小时制，秒数通常是00，取值范围从 00:00:00 到 23:59:00。由于SmallDateTime会把时间近似到分钟，秒数通常是0，且不符ANSI和 ISO 8601，因此，在实际的开发中使用的比较少，建议使用time, date, datetime2 和 datetimeoffset来用于新的开发程序中。

select cast(getdate() as smalldatetime) as now_sdt, cast(getdate() as datetime) as now_dt

SQL Server的日期和时间类型

二，DateTimeOffset类型

DateTimeOffset(n)数据类型由三部分构成：date、time和 offset（时区偏移），包含了日期、时间和时区数据，其日期和时间使用是本地时间。在本地时间的基础上，使用时区偏移量（offset）来计算UTC时间，因此，DateTimeOffset(n)可以同时表示本地时间和UTC时间，默认的显示文本是：YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]，默认值是1900-01-01 00:00:00 00:00。

DateTimeOffset(n)能够表示的日期、时间和时区范围是：

  • 表示的日期范围是：0001-01-01 到 9999-12-31，
  • 表示的时间范围是：00:00:00 到 23:59:59.9999999，表示的时间精度是100ns，
  • 表示的时区范围是：-14:00 到 +14:00

小数秒的精度会影响变量占用的存储空间的大小：

  • 当小数秒的精度n是 0-2 时，存储大小是8B
  • 当小数秒的精度n是 3-4 时，存储大小是9B
  • 当小数秒的精度n是 5-7 时，存储大小是10B，小数秒的默认精度是7

时区偏移的格式为：[+|-] hh:mm，hh 表示的小时范围是00-14，mm 表示的分钟范围是00-59，把DateTimeOffset时间中的UTC时间 和 时区偏移量 进行算术运算，可以得到本地时间。

1，返回本地时间的DateTimeOffset

返回本地的时间和时区，本地时间是2018-08-29 11:11:29.5765832，本地所在的时区是东八区：

SELECT SYSDATETIMEOFFSET ( )

SQL Server的日期和时间类型

2，把DateTime2时间转换为DateTimeOffset时间

向DateTime2时间中添加时区信息：

TODATETIMEOFFSET ( expression , time_zone )

参数注释：

  • expression：是DateTime2类型的值，
  • time_zone：是时区的偏移量，格式是 [+|-] hh:mm

例如，使用SYSDATETIME得到本地的DateTime2，添加时区（东八区），得到的包含时区信息的DateTimeOffset：

SELECT TODATETIMEOFFSET(SYSDATETIME(),'+08:00'),SYSDATETIMEOFFSET()

SQL Server的日期和时间类型

3，切换时区

把DateTimeOffset类型的数据切换到指定的时区，在转换过程中，UTC时间是固定的，依据固定的UTC时间，切换到特定时区的本地时间：

SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

参数注释：

  • DATETIMEOFFSET：DateTimeOffset(n)类型的变量
  • time_zone：指定的目标时区数据，格式是  [+|-] hh:mm

例如，把本地时间的时区东八区切换到东七区：

DECLARE @remote DATETIMEOFFSET

DECLARE @local DATETIMEOFFSET

SET @local = SYSDATETIMEOFFSET()

SET @remote = SWITCHOFFSET (@local, '+07:00')

SELECT @remote AS remote_time,@local AS local_time

SQL Server的日期和时间类型

可以看到，东7区的时间比东8区的时间晚一个小时。

4，丢弃时区信息

DateTime2表示的是本地时间，DateTimeOffset表示的是本地时间和本地的时区，如果数据不需要考虑时区信息，那么可以直接把DateTimeOffset时间赋值给DateTime2时，SQL Server执行隐式转换，把时区信息丢弃，只把本地时间赋值给DateTime2：

DECLARE @datetimeoffset DATETIMEOFFSET(4)

DECLARE @datetime2 DATETIME2(3)=@datetimeoffset;  

SET @datetimeoffset= '2012-10-25 12:24:32.1277 +01:00'

SET @datetime2=@datetimeoffset

SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';

SQL Server的日期和时间类型

三，日期和时间类型占用的字节数

DataLength能过返回任意数据类型的变量所占用的字节数量，从下图中可以看到，datetime占用的存储空间太大，精度不高，datetime2完胜datetime，在产品环境中，推荐使用datetime2：

declare @dt1 datetime

declare @dt2 datetime2(2)

declare @dt3 datetime2(4)

set @dt1=getdate()

set @dt2=getdate()

set @dt3=SYSDATETIME()

select DATALENGTH(@dt1),DATALENGTH(@dt2),DATALENGTH(@dt3),@dt1,@dt2,@dt3

SQL Server的日期和时间类型

如果对time的要求不是很高，保留2位毫秒，使用datetime2(2)，比其他类型节省存储空间。

四，SQL Server 的本地时间和UTC时间

本地时间：世界的每个地区都有自己的本地时间，整个地球分为二十四时区，每个时区都有自己的本地时间。

UTC时间：在国际无线电通信中，为统一而普遍使用一个标准时间，称为通用协调时(UTC, Universal Time Coordinated)。UTC时间和英国伦敦的本地时间相同。UTC时间在世界各地都是相同的，但是不同的时区，本地时间各不相同。根据UTC时间和所在的时区，能够计算出本地时间。

1，获取本地时间和UTC时间

在SQL Sever中，GetDate()返回的是本地时间，GetUTCDate()返回的是UTC时间，返回的数据类型是DateTime；SysDateTime() 返回的是本地时间，SysUTCDateTime() 返回的是UTC时间，返回的数据类型是DateTime2(7)。

在不同的时区，UTC时间相同，但是本地时间不同。本地时间是Server上显示的系统时间，在OS中变更时区（time zone），能够修改本地时间和GetDate()的返回值。本地时间是根据UTC时间和OS设置的时区推导出来的。

结论：GetDate() 和SysDateTime() 返回的是本地Server的Date和Time，这个值跟SQL  Server实例本地主机的OS有关，跟OS显示的时间是相同的。

2，SQL Agent 执行Job的时间

在SQL Server Agent中使用的时间是本地时间，Job的执行时间是本地时间。

五，对DateDiff()函数的正确理解

此函数返回在开始日期和结束日期之间对于datepart边界的计数:

DATEDIFF ( datepart , startdate , enddate )

当我们指定datepart 时，无论是年、月、小时还是分钟，数据库引擎比较的数据都不会超出该部分。 因此，如果我们指定月份，则引擎只比较年份和月份，但不会做进一步的比较。这些值可能相隔仅一秒钟，但是引擎关心的只是年份和月份。

举个例子，下面的脚本返回的值都是1，虽然两个时间只差100纳秒，但是当比较的datepart边界大于100ns时，都只会返回1个datepart单位。

SELECT DATEDIFF(year,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')

    , DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

六，12小时制和24小时制

十二小时制是大多数指针式钟表显示时间的方法，注意钟面上数字没有0，但有12，所以起点是12。24小时制的0:30，在钟面上读成12:30，再用AM表示上午，用PM表示下午。

SQL Server的日期和时间类型

对于凌晨12点，用12小时制表示是12:00 AM，用24小时制表示是00:00。

对于中午12点，用12小时制表示是12:00 PM，用24小时制表示是12:00。

对于凌晨30分，用12小时制表示是12:30 AM，用24小时制表示是00:30。

参考文档：

Date and Time Data Types and Functions (Transact-SQL)

DATALENGTH (Transact-SQL)

相关文章