SQL Server 日期和時間的內部存儲過程

2020-07-25 11:52:23

字體：大中小

來源：轉載

供稿：網友

在SQL Server的內部存儲中，日期和時間不是以字符串的形式存儲的，而是使用整數來存儲的。使用特定的格式來區(qū)分日期部分和時間部分的偏移量，并通過基準日期和基準時間來還原真實的數據。

一，DateTime的內部存儲

SQL Server存儲引擎把DateTime類型存儲為2個int32類型，共8個字節(jié)，第一個int32 整數（前4個字節(jié)）存儲的是日期相對于基準日期（1900-01-01）的偏移量。基準日期是1900-01-01，當前4 字節(jié)為0 時，表示的日期是1900 年1 月1 日。第二個int32整數（后4個字節(jié)）存儲的是午夜（00:00:00.000）之后的時鐘滴答數，每個滴答為1⁄300秒，精確度為3.33毫秒（0.00333秒，3.33ms），因此，DateTime能夠表示的時間，可能會存在一個滴答的時間誤差。

DateTime的內部存儲格式，用十六進制表示是：DDDDTTTT

DDDD：占用2個字節(jié)，表示對基準日期的偏移量
TTTT：占用兩個字節(jié)，表示對午夜之后的始終滴答數

舉個例子，對于如下的日期和時間，把DateTime類型轉換為大小為8個字節(jié)的16進制，每兩個數字對應1個字節(jié)：

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

1，拆分出date和time

把時間的二進制格式中的字節(jié)拆分成兩部分：前4個字節(jié)表示date，后4個字節(jié)表示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)內部存儲的第一個字節(jié)存儲精度n，后續(xù)的字節(jié)用于存儲日期和時間的值。
當小數秒的精度 n < 3 時，總的存儲空間是1B（精度）+6 B（數據）；
當小數秒的精度 n 是 3 - 4 時，總的存儲空間是1B（精度）+ 7B（數據）；
當小數秒的精度 n 是 5 - 7 時，總的存儲空間是1B（精度）+ 8B（數據），最大的小數秒精度是7，默認值是7；

1，二進制逆序

在探索DateTime2(n)的內部存儲之前，先了解一下字節(jié)存儲的“小端”格式和“大端”格式：

大端格式：是指數據的低位保存在內存的高地址中，而數據的高位，保存在內存的低地址中；
小端格式：是指數據的低位保存在內存的低地址中，而數據的高位保存在內存的高地址中。

舉個例子，假如內存地址左邊是地位，右邊是高位，對于數字275，使用兩個字節(jié)來存儲：

如果采用大端格式：字節(jié)序列是0x0113
如果采用小端格式：字節(jié)序列是0x1301

DateTime2(n)的內部存儲格式使用的是小端格式，這種格式適合CPU的運算。

2，DateTime2的存儲格式

DateTime2(n)的內部存儲格式是：

第一字節(jié)存儲的精度n，
后三個字節(jié)記錄從基準日期0001-01-01之后的多少天，采用小端格式。
中間余下的字節(jié)記錄子夜之后經過的時間單位間隔（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) 的字節(jié)流逆序排列：前3個字節(jié)表示的是天數，最后一個字節(jié)表示的是精度，中間余下的字節(jié)表示的TUI的數量。例如，對于 DateTime2(7)按照字節(jié)流逆序處理之后，存儲空間是9個字節(jié)：前三個字節(jié)是存儲的從基準日期0001-01-01之后的多少天，最后一位是精度n，中間的5個字節(jié)表示從子夜開始有多少個TUI。

2，把DateTime2轉換為二進制存儲

把DateTime2轉換為二進制存儲，并作逆序處理，DateTime2(3)的精度為3，存儲空間是8個字節(jié)，后三個字節(jié)記錄從基準日期0001-01-01之后的多少天，前3個字節(jié)表示從子夜開始有多少個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