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)