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

 更新时间:2020年7月11日 17:36  点击:1831

在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 日期和时间的内部存储,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对猪先飞网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

[!--infotagslink--]

相关文章

  • C#连接SQL数据库和查询数据功能的操作技巧

    本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧,本文通过图文并茂的形式给大家介绍的非常详细,需要的朋友参考下吧...2021-05-17
  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 在java中获取List集合中最大的日期时间操作

    这篇文章主要介绍了在java中获取List集合中最大的日期时间操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-08-15
  • PostgreSQL判断字符串是否包含目标字符串的多种方法

    这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
  • 教你怎么用Java获取国家法定节假日

    这篇文章主要介绍了教你怎么用Java获取国家法定节假日,文中有非常详细的代码示例,对正在学习java的小伙伴们有非常好的帮助,需要的朋友可以参考下...2021-04-23
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • PostgreSQL TIMESTAMP类型 时间戳操作

    这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
  • .NET/C# 使用Stopwatch测量运行时间

    这篇文章主要介绍了.NET/C# 使用Stopwatch测量运行时间,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-06-25
  • postgresql 实现多表关联删除

    这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
  • Vue基于localStorage存储信息代码实例

    这篇文章主要介绍了Vue基于localStorage存储信息代码实例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-11-16
  • Postgresql 如何选择正确的关闭模式

    这篇文章主要介绍了Postgresl 如何选择正确的关闭模式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
  • mysql中获取一天、一周、一月时间数据的各种sql语句写法

    创建表:复制代码 代码如下:create table if not exists t( id int, addTime datetime default '0000-00-00 00:00:00′)添加两条初始数据:insert t values(1, '2012-07-12 21:00:00′);insert t values(2, '2012-07...2014-05-31
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • postgresql数据添加两个字段联合唯一的操作

    这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
  • JS日期加减,日期运算代码

    一、日期减去天数等于第二个日期function cc(dd,dadd){//可以加上错误处理var a = new Date(dd)a = a.valueOf()a = a - dadd * 24 * 60 * 60 * 1000a = new Date(a)alert(a.getFullYear() + "年" + (a.getMonth() +...2015-11-08
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • php计算两个日期相差天数的方法

    本文实例讲述了php计算两个日期相差天数的方法。...2015-03-15
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • 常用的日期时间正则表达式

    常用的日期时间正则表达式 下面收藏了大量的日期时间正则匹配函数,包括分钟,时间与秒都能达到。 正则表达式 (?n:^(?=d)((?<day>31(?!(.0?[2469]|11))|30(?!.0?2)|29(...2016-11-25