艾锑知识 | SQL Server 日期和时间的内部存储过程

2020-03-08 19:00 作者：艾锑无限 浏览量：

   
2015年我第一次参加阿里云杭州全球云栖大会时，看到的是惊喜和无限的可能性。
 
“2015杭州云栖大会”于10月14日至15日在杭州云栖小镇如期召开。大会以“互联网、创新、创业”为本届主题，展现“互联网+”时代下无处不在的云计算与各行各业的交错连接，介绍云计算为产业升级和改革创新提供的源源动力，挖掘云计算助力下生生不息的创业激情和机遇。
 
2015年正式更名为“云栖大会”，阿里云的名字不再出现在会议名称中，而是更加强调云计算、大数据生态的定位。量子计算、人工智能、生物识别、深度学习等前沿的科技创新力量首次在大会亮相。大会吸引了全球超过20个国家的21500名开发者，参展企业达到219家，参与企业3000多家，现场参观超过42584人次，全球直播收看人数超过127万人，成为全球最大规模的云计算峰会之一。
 
走进会场别开生面，丰富多彩，让人忘掉了科技的冰冷，就像走进了游乐园，有运动，有演出，有游戏，有展示，有互动，4万多人把整个会场挤得水泄不通，来自全球的技术爱好者，企业家，科学家，政府官员，阿里云的合作伙伴，竞争对手，闲杂人等五花八门，你能想到的人都聚齐了。
 
甚至还有票贩子，是什么吸引了各路好汉齐聚一堂，挤破头也要参加一场听起来非常专业性的云栖大会呢?
 
除了上面说的阿里云的技术能力和运营能力以外，其实还有一个非常重要的原因，就是能亲眼见到马云马老师，甚至还能有机会与他零距离接触.
 
从2015到2018年，在这四年中马老师每年都会参加杭州云栖大会的分享，而且还会出现在云栖现场的不同地方，这让很多喜欢马老师的伙伴兴奋极了，你想你一生能有几次亲眼见到“外星人”呢.


2015年他在主会场20分钟的分享表达了一个重要的观点，就是在未来5年无企业，不上云。上云是企业战略而不是战术，未来是一个万物互联的时代，只有那些在云上的企业才能快速有效的抓住时代变化的先机，才能使用计算的能力，才能利用好数据的价值.
 
当时听了马老师的演讲，我当刻就决定了和阿里云的合作，让艾锑无限成为了阿里云的战略合作伙伴，和阿里云一起为中小企业提供上云解决方案服务。
 
四年多过去了，马老师当年说无企业，不上云，如今，如果你不知道云，你的企业没有用云，可能你在这次的疫情中根本就没有战斗力，你都不知道别人企业是如何赢得这次疫情战斗胜利的.
 
企业上云不仅能为企业节省70%以上的成本，更能为企业提升500%以上的效率，甚至它的有些价值是无法计算的，这些年艾锑无限为上千家企业提供了上云服务，亲身经历了有一些企业上云变革后带来的增长和变化，我记得服务了一家装修行业的领头企业，在2016年这家企业因人员场地和机房成本，让这家公司举步维艰，离破产仅一步之遥，我和这家企业的CEO是好朋友，有一天我去看他，听到他和我分享现在遇到的巨大困境，我问了他几个关键的问题，最后确认了是成本不断提升，业绩却没有提高，效率低下从而导致企业经营困难，后来艾锑无限的云解决方案团队帮助他出了三个解决方案:
 
把他们现有机房近百台服务器迁到云上，释放出近百平米的机房空间和硬件运维升级成本，因为云是弹性的，可以根据企业用量来支付费用，这一下就帮他们减轻了一年上百万的硬件投入成本.
 
帮助他们打通各个管理系统，让企业内部数据更透明，让整体效率一下子就提升了上来，而且当他发现整个环节全部通透后，有1/3的人力是可以节省的，完全没必要用这么多人，这一下让他们每年节省了近200万的人力支出，这就是科技的力量，虽然对失业的员工有些残忍，但对与一家商业企业来说这是正确的选择.
 
重建营销系统，让传统的销售模式重新变革，提升人员的产出比，让全员进行销售，整体销售额提高了200%，这个时代职责和名称将会越来越模糊，谁能为企业创造价值，谁就是企业最重要的员工.
 
大家可以想象一下，一家企业人员降低原来的1/3，但销售额却提升原来的两倍，这意味着盈利能力和盈利水平呈指数级的增长，这就是变革后的成果.
 
无企业，不上云，你的企业上云了吗？

 
如果你的企业还没有下云也没有关系，也许你的企业还没有遇到挑战，假如你有和我朋友同样的困境，那不妨与艾锑无限的云工程师和技术专家聊一聊，说不定就让你眼前一亮，打开了你遇到的限制和困境，从而让你的企业迈上了生长的第二春。

艾锑知识 |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个字节：

1 2 3

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，得出的结果如下：

1 2 3 4 5 6

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，通过偏移量还原日期和时间

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

1 2 3 4 5

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。

1 2 3 4

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)的各个组成成分：

1 2 3 4 5 6 7 8 9 10 11

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，利用偏移量和基准还原原始值

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

1 2 3 4 5

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 日期和时间的内部存储，希望对大家有所帮助