SQL Server 日期和时间的内部存储过程

在sql server的内部存储中，日期和时间不是以字符串的形式存储的，而是使用整数来存储的。使用特定的格式来区分日期部分和时间部分的偏移量，并通过基准日期和基准时间来还原真实的数据。

一，datetime的内部存储

sql server存储引擎把datetime类型存储为2个int32类型，共8个字节，第一个int32 整数（前4个字节）存储的是日期相对于基准日期（1900-01-01）的偏移量。基准日期是1900-01-01，当前4 字节为0 时，表示的日期是1900 年1 月1 日。第二个int32整数（后4个字节）存储的是午夜（00:00:00.000）之后的时钟滴答数，每个滴答为1⁄300秒，精确度为3.33毫秒（0.00333秒，3.33ms），因此，datetime能够表示的时间，可能会存在一个滴答的时间误差。

datetime的内部存储格式，用十六进制表示是：ddddtttt

• dddd：占用2个字节，表示对基准日期的偏移量
• tttt：占用两个字节，表示对午夜之后的始终滴答数

举个例子，对于如下的日期和时间，把datetime类型转换为大小为8个字节的16进制，每两个数字对应1个字节：

declare @dt datetime = '2015-05-07 10:05:23.187'
select convert(varbinary(8), @dt) as date_time_binary
--output 0x0000a49100a6463c

1，拆分出date和time

把时间的二进制格式中的字节拆分成两部分：前4个字节表示date，后4个字节表示time，得出的结果如下：

declare @dt datetime = '2015-05-07 10:05:23.187'

select substring(convert(varbinary(8), @dt), 1, 4) as date_binary,
 cast(substring(convert(varbinary(8), @dt), 1, 4) as int) as date_int,
 substring(convert(varbinary(8), @dt), 5, 4) as time_binary,
 cast(substring(convert(varbinary(8), @dt), 5, 4) as int) as time_int;

 

2，通过偏移量还原日期和时间

通过基准时间和偏移量，把整数还原为原始的日期和时间：

declare @time time='00:00:00.000'
declare @date date='1900-01-01'

select dateadd(day, 42129, @date) as originl_date
 , dateadd(ms,10896956*10/3, @time) as original_time

二，datetime2的内部存储

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

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

1，二进制逆序

在探索datetime2(n)的内部存储之前，先了解一下字节存储的“小端”格式和“大端”格式：

• 大端格式：是指数据的低位保存在内存的高地址中，而数据的高位，保存在内存的低地址中；
• 小端格式：是指数据的低位保存在内存的低地址中，而数据的高位保存在内存的高地址中。
  

举个例子，假如内存地址左边是地位，右边是高位，对于数字275，使用两个字节来存储：

• 如果采用大端格式：字节序列是0x0113
• 如果采用小端格式：字节序列是0x1301
  

datetime2(n)的内部存储格式使用的是小端格式，这种格式适合cpu的运算。

2，datetime2的存储格式

datetime2(n)的内部存储格式是：

• 第一字节存储的精度n，
• 后三个字节记录从基准日期0001-01-01之后的多少天，采用小端格式。
• 中间余下的字节记录子夜之后经过的时间单位间隔（time unit interval，tui）的数量，采用小端格式。

tui是由精度来控制的，每一个tui是10的n次方之一秒，也就是：

• 对于 datetime2(7)，tui是100ns；
• 对于 datetime2(6)，tui是1微秒（=1000ns）；
• 对于 datetime2(5)，tui是10微秒；
• 对于 datetime2(4)，tui是100微秒；
• 对于 datetime2(3)，tui是1ms（1毫秒=1000微秒）；

为了便于运算，把datetime2(n) 的字节流逆序排列：前3个字节表示的是天数，最后一个字节表示的是精度，中间余下的字节表示的tui的数量。例如，对于 datetime2(7)按照字节流逆序处理之后，存储空间是9个字节：前三个字节是存储的从基准日期0001-01-01之后的多少天，最后一位是精度n，中间的5个字节表示从子夜开始有多少个tui。

2，把datetime2转换为二进制存储

把datetime2转换为二进制存储，并作逆序处理，datetime2(3)的精度为3，存储空间是8个字节，后三个字节记录从基准日期0001-01-01之后的多少天，前3个字节表示从子夜开始有多少个tui。

declare @dt datetime2(3)='2015-05-07 10:05:23.187'
declare @dt_bi varbinary(max)=convert(varbinary(max), @dt) 
select @dt_bi as date_time_binary
 ,convert(varbinary(max),reverse(@dt_bi)) as reverse_binary

把二进制值拆分成datetime2(3)的各个组成成分：

declare @dt datetime2(3)='2015-05-07 10:05:23.187'
declare @dt_bi varbinary(max)=convert(varbinary(max), @dt) 
declare @dt_bi_littleend varbinary(max)
select @dt_bi_littleend=convert(varbinary(max),reverse(@dt_bi))

select substring(convert(varbinary(8), @dt_bi_littleend), 1, 3) as date_binary,
 cast(substring(convert(varbinary(8), @dt_bi_littleend), 1, 3) as int) as date_int,
 substring(convert(varbinary(8), @dt_bi_littleend), 4, 4) as time_binary,
 cast(substring(convert(varbinary(8), @dt_bi_littleend), 4, 4) as int) as time_int,
 substring(convert(varbinary(8), @dt_bi_littleend), 8, 1) as precision_binary,
 cast(substring(convert(varbinary(8), @dt_bi_littleend), 8, 1) as int) as precision_int;

3，利用偏移量和基准还原原始值

有了偏移量，就可以在基准日期和时间之上加上偏移量来获得原始值：

declare @time time='00:00:00.000'
declare @date date='0001-01-01'

select dateadd(day, 735724, @date) as originl_date
 , dateadd(ms,36323187, @time) as original_time

参考文档：

what is the sql server 2008 datetime2 internal structure?

how to get sql server dates and times horribly wrong

总结

以上所述是小编给大家介绍的sql server 日期和时间的内部存储，希望对大家有所帮助