sqlserver带参数的存储过程(SQL Server 日期和时间的内部存储过程)
sqlserver带参数的存储过程
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 日期和时间的内部存储,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!
- 数据库having的用法(数据库SQL中having和where的用法区别)
- sqlserver如何添加数据库(sqlserver建立新用户及关联数据库教程)
- mysql对大表千万级如何优化(MySQL 大表的count优化实现)
- sqlserverdate格式比较(sqlserver之datepart和datediff应用查找当天上午和下午的数据)
- mysql视图管理方法(MySQL 视图View原理解析)
- SQL语句中单引号
- mysql中null的用法(为什么mysql字段要使用NOT NULL)
- mysqlint后面的4是什么意思(MySQL中intn后面的n到底代表的是什么意思)
- mysql怎么删除用户操作(解决mysql删除用户 bug的问题)
- SQL Server中对列的权限设置
- SQL Server数据库备份的几种方式
- mysql发生系统错误(MySQL too many connections错误的原因及解决)
- SQL SERVER存储过程调试
- 怎么知道sqlyog连接的哪个mysql(SQLyog连接MySQL8.0报2058错误的完美解决方法)
- plsql常用函数
- sql取值唯一的方法(SQL 获取所有上级的实现方法)
- 冰岛旅游攻略(冰岛旅游攻略及花费)
- 为什么现在年轻人越来越喜欢买衣服(为什么现在年轻人越来越喜欢买衣服穿)
- 怎么做好SEO(怎么做好seo内容优化)
- 冬季钓鱼子线用 长 还是 短(冬季钓鱼子线用)
- 鱼竿 夏钓短,冬钓长 ,一定是这样 认清优缺点在选竿(鱼竿夏钓短冬钓长)
- 鲢鳙钓底还是钓浮 流水的水域应怎样做钓(鲢鳙钓底还是钓浮)
热门推荐
- sqlserver存储过程参数默认值(sql server使用临时存储过程实现使用参数添加文件组脚本复用)
- dedecms安全验证(dede验证码错误 dede验证码一直提示错误的解决方法)
- php代码打包环境加密(php实现对文件压缩简单的方法)
- pythonredis列表(Python redis操作实例分析连接、管道、发布和订阅等)
- 超级小旋风asp服务器软件使用图文教程(超级小旋风asp服务器软件使用图文教程)
- dedecms路径设置(dedecms V5.7修改表前缀的方法及出现不显示文章内容的解决方法)
- linux下启动tomcat服务(Linux系统安装Tomcat并配置Service启动关闭)
- ftp的使用方法(教你轻松玩转FTP)
- thinkphp分页效果怎样(thinkphp5+layui实现的分页样式示例)
- python中的变量与变量类型(Python变量类型知识点总结)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9