在SQL Server的內(nèi)部存儲中,日期和時間不是以字符串的形式存儲的,而是使用整數(shù)來存儲的。使用特定的格式來區(qū)分日期部分和時間部分的偏移量,并通過基準(zhǔn)日期和基準(zhǔn)時間來還原真實(shí)的數(shù)據(jù)。
一,DateTime的內(nèi)部存儲
SQL Server存儲引擎把DateTime類型存儲為2個int32類型,共8個字節(jié),第一個int32 整數(shù)(前4個字節(jié))存儲的是日期相對于基準(zhǔn)日期(1900-01-01)的偏移量。基準(zhǔn)日期是1900-01-01,當(dāng)前4 字節(jié)為0 時,表示的日期是1900 年1 月1 日。第二個int32整數(shù)(后4個字節(jié))存儲的是午夜(00:00:00.000)之后的時鐘滴答數(shù),每個滴答為1⁄300秒,精確度為3.33毫秒(0.00333秒,3.33ms),因此,DateTime能夠表示的時間,可能會存在一個滴答的時間誤差。
DateTime的內(nèi)部存儲格式,用十六進(jìn)制表示是:DDDDTTTT
- DDDD:占用2個字節(jié),表示對基準(zhǔn)日期的偏移量
- TTTT:占用兩個字節(jié),表示對午夜之后的始終滴答數(shù)
舉個例子,對于如下的日期和時間,把DateTime類型轉(zhuǎn)換為大小為8個字節(jié)的16進(jìn)制,每兩個數(shù)字對應(yīng)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
把時間的二進(jìn)制格式中的字節(jié)拆分成兩部分:前4個字節(jié)表示date,后4個字節(jié)表示time,得出的結(jié)果如下:
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,通過偏移量還原日期和時間
通過基準(zhǔn)時間和偏移量,把整數(shù)還原為原始的日期和時間:
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的內(nèi)部存儲
DateTime2(n)數(shù)據(jù)類型存儲日期和時間,它是DateTime的升級版本,由于小數(shù)秒n的精度可以自主設(shè)置,其存儲大?。⊿torage Size)不固定,DateTime2(n)占用的存儲空間和小數(shù)秒的精度之間的關(guān)系是:
- DateTime2(n)內(nèi)部存儲的第一個字節(jié)存儲精度n,后續(xù)的字節(jié)用于存儲日期和時間的值。
- 當(dāng)小數(shù)秒的精度 n 3 時,總的存儲空間是1B(精度)+6 B(數(shù)據(jù));
- 當(dāng)小數(shù)秒的精度 n 是 3 - 4 時,總的存儲空間是1B(精度)+ 7B(數(shù)據(jù));
- 當(dāng)小數(shù)秒的精度 n 是 5 - 7 時,總的存儲空間是1B(精度)+ 8B(數(shù)據(jù)),最大的小數(shù)秒精度是7,默認(rèn)值是7;
1,二進(jìn)制逆序
在探索DateTime2(n)的內(nèi)部存儲之前,先了解一下字節(jié)存儲的“小端”格式和“大端”格式:
- 大端格式:是指數(shù)據(jù)的低位保存在內(nèi)存的高地址中,而數(shù)據(jù)的高位,保存在內(nèi)存的低地址中;
- 小端格式:是指數(shù)據(jù)的低位保存在內(nèi)存的低地址中,而數(shù)據(jù)的高位保存在內(nèi)存的高地址中。
舉個例子,假如內(nèi)存地址左邊是地位,右邊是高位,對于數(shù)字275,使用兩個字節(jié)來存儲:
- 如果采用大端格式:字節(jié)序列是0x0113
- 如果采用小端格式:字節(jié)序列是0x1301
DateTime2(n)的內(nèi)部存儲格式使用的是小端格式,這種格式適合CPU的運(yùn)算。
2,DateTime2的存儲格式
DateTime2(n)的內(nèi)部存儲格式是:
- 第一字節(jié)存儲的精度n,
- 后三個字節(jié)記錄從基準(zhǔn)日期0001-01-01之后的多少天,采用小端格式。
- 中間余下的字節(jié)記錄子夜之后經(jīng)過的時間單位間隔(time unit interval,TUI)的數(shù)量,采用小端格式。
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微秒);
為了便于運(yùn)算,把DateTime2(n) 的字節(jié)流逆序排列:前3個字節(jié)表示的是天數(shù),最后一個字節(jié)表示的是精度,中間余下的字節(jié)表示的TUI的數(shù)量。例如,對于 DateTime2(7)按照字節(jié)流逆序處理之后,存儲空間是9個字節(jié):前三個字節(jié)是存儲的從基準(zhǔn)日期0001-01-01之后的多少天,最后一位是精度n,中間的5個字節(jié)表示從子夜開始有多少個TUI。
2,把DateTime2轉(zhuǎn)換為二進(jìn)制存儲
把DateTime2轉(zhuǎn)換為二進(jìn)制存儲,并作逆序處理,DateTime2(3)的精度為3,存儲空間是8個字節(jié),后三個字節(jié)記錄從基準(zhǔn)日期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
把二進(jìn)制值拆分成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,利用偏移量和基準(zhǔn)還原原始值
有了偏移量,就可以在基準(zhǔn)日期和時間之上加上偏移量來獲得原始值:
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
總結(jié)
以上所述是小編給大家介紹的SQL Server 日期和時間的內(nèi)部存儲,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持!
如果你覺得本文對你有幫助,歡迎轉(zhuǎn)載,煩請注明出處,謝謝!
您可能感興趣的文章:- 解析MySQL中存儲時間日期類型的選擇問題
- sql server動態(tài)存儲過程按日期保存數(shù)據(jù)示例
- SQL查詢出表、存儲過程、觸發(fā)器的創(chuàng)建時間和最后修改時間示例
- 返回SQL執(zhí)行時間的存儲過程