艾銻知識(shí) | SQL Server 日期和時(shí)間的內(nèi)部存儲(chǔ)過程

2020-03-08 19:00 作者：艾銻無限 瀏覽量：

   
2015年我第一次參加阿里云杭州全球云棲大會(huì)時(shí)，看到的是驚喜和無限的可能性。
 
“2015杭州云棲大會(huì)”于10月14日至15日在杭州云棲小鎮(zhèn)如期召開。大會(huì)以“互聯(lián)網(wǎng)、創(chuàng)新、創(chuàng)業(yè)”為本屆主題，展現(xiàn)“互聯(lián)網(wǎng)+”時(shí)代下無處不在的云計(jì)算與各行各業(yè)的交錯(cuò)連接，介紹云計(jì)算為產(chǎn)業(yè)升級(jí)和改革創(chuàng)新提供的源源動(dòng)力，挖掘云計(jì)算助力下生生不息的創(chuàng)業(yè)激情和機(jī)遇。
 
2015年正式更名為“云棲大會(huì)”，阿里云的名字不再出現(xiàn)在會(huì)議名稱中，而是更加強(qiáng)調(diào)云計(jì)算、大數(shù)據(jù)生態(tài)的定位。量子計(jì)算、人工智能、生物識(shí)別、深度學(xué)習(xí)等前沿的科技創(chuàng)新力量首次在大會(huì)亮相。大會(huì)吸引了全球超過20個(gè)國家的21500名開發(fā)者，參展企業(yè)達(dá)到219家，參與企業(yè)3000多家，現(xiàn)場參觀超過42584人次，全球直播收看人數(shù)超過127萬人，成為全球最大規(guī)模的云計(jì)算峰會(huì)之一。
 
走進(jìn)會(huì)場別開生面，豐富多彩，讓人忘掉了科技的冰冷，就像走進(jìn)了游樂園，有運(yùn)動(dòng)，有演出，有游戲，有展示，有互動(dòng)，4萬多人把整個(gè)會(huì)場擠得水泄不通，來自全球的技術(shù)愛好者，企業(yè)家，科學(xué)家，政府官員，阿里云的合作伙伴，競爭對(duì)手，閑雜人等五花八門，你能想到的人都聚齊了。
 
甚至還有票販子，是什么吸引了各路好漢齊聚一堂，擠破頭也要參加一場聽起來非常專業(yè)性的云棲大會(huì)呢?
 
除了上面說的阿里云的技術(shù)能力和運(yùn)營能力以外，其實(shí)還有一個(gè)非常重要的原因，就是能親眼見到馬云馬老師，甚至還能有機(jī)會(huì)與他零距離接觸.
 
從2015到2018年，在這四年中馬老師每年都會(huì)參加杭州云棲大會(huì)的分享，而且還會(huì)出現(xiàn)在云棲現(xiàn)場的不同地方，這讓很多喜歡馬老師的伙伴興奮極了，你想你一生能有幾次親眼見到“外星人”呢.


2015年他在主會(huì)場20分鐘的分享表達(dá)了一個(gè)重要的觀點(diǎn)，就是在未來5年無企業(yè)，不上云。上云是企業(yè)戰(zhàn)略而不是戰(zhàn)術(shù)，未來是一個(gè)萬物互聯(lián)的時(shí)代，只有那些在云上的企業(yè)才能快速有效的抓住時(shí)代變化的先機(jī)，才能使用計(jì)算的能力，才能利用好數(shù)據(jù)的價(jià)值.
 
當(dāng)時(shí)聽了馬老師的演講，我當(dāng)刻就決定了和阿里云的合作，讓艾銻無限成為了阿里云的戰(zhàn)略合作伙伴，和阿里云一起為中小企業(yè)提供上云解決方案服務(wù)。
 
四年多過去了，馬老師當(dāng)年說無企業(yè)，不上云，如今，如果你不知道云，你的企業(yè)沒有用云，可能你在這次的疫情中根本就沒有戰(zhàn)斗力，你都不知道別人企業(yè)是如何贏得這次疫情戰(zhàn)斗勝利的.
 
企業(yè)上云不僅能為企業(yè)節(jié)省70%以上的成本，更能為企業(yè)提升500%以上的效率，甚至它的有些價(jià)值是無法計(jì)算的，這些年艾銻無限為上千家企業(yè)提供了上云服務(wù)，親身經(jīng)歷了有一些企業(yè)上云變革后帶來的增長和變化，我記得服務(wù)了一家裝修行業(yè)的領(lǐng)頭企業(yè)，在2016年這家企業(yè)因人員場地和機(jī)房成本，讓這家公司舉步維艱，離破產(chǎn)僅一步之遙，我和這家企業(yè)的CEO是好朋友，有一天我去看他，聽到他和我分享現(xiàn)在遇到的巨大困境，我問了他幾個(gè)關(guān)鍵的問題，最后確認(rèn)了是成本不斷提升，業(yè)績卻沒有提高，效率低下從而導(dǎo)致企業(yè)經(jīng)營困難，后來艾銻無限的云解決方案團(tuán)隊(duì)幫助他出了三個(gè)解決方案:
 
把他們現(xiàn)有機(jī)房近百臺(tái)服務(wù)器遷到云上，釋放出近百平米的機(jī)房空間和硬件運(yùn)維升級(jí)成本，因?yàn)樵剖菑椥缘模梢愿鶕?jù)企業(yè)用量來支付費(fèi)用，這一下就幫他們減輕了一年上百萬的硬件投入成本.
 
幫助他們打通各個(gè)管理系統(tǒng)，讓企業(yè)內(nèi)部數(shù)據(jù)更透明，讓整體效率一下子就提升了上來，而且當(dāng)他發(fā)現(xiàn)整個(gè)環(huán)節(jié)全部通透后，有1/3的人力是可以節(jié)省的，完全沒必要用這么多人，這一下讓他們每年節(jié)省了近200萬的人力支出，這就是科技的力量，雖然對(duì)失業(yè)的員工有些殘忍，但對(duì)與一家商業(yè)企業(yè)來說這是正確的選擇.
 
重建營銷系統(tǒng)，讓傳統(tǒng)的銷售模式重新變革，提升人員的產(chǎn)出比，讓全員進(jìn)行銷售，整體銷售額提高了200%，這個(gè)時(shí)代職責(zé)和名稱將會(huì)越來越模糊，誰能為企業(yè)創(chuàng)造價(jià)值，誰就是企業(yè)最重要的員工.
 
大家可以想象一下，一家企業(yè)人員降低原來的1/3，但銷售額卻提升原來的兩倍，這意味著盈利能力和盈利水平呈指數(shù)級(jí)的增長，這就是變革后的成果.
 
無企業(yè)，不上云，你的企業(yè)上云了嗎？

 
如果你的企業(yè)還沒有下云也沒有關(guān)系，也許你的企業(yè)還沒有遇到挑戰(zhàn)，假如你有和我朋友同樣的困境，那不妨與艾銻無限的云工程師和技術(shù)專家聊一聊，說不定就讓你眼前一亮，打開了你遇到的限制和困境，從而讓你的企業(yè)邁上了生長的第二春。

艾銻知識(shí) |SQL Server 日期和時(shí)間的內(nèi)部存儲(chǔ)過程


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

一，DateTime的內(nèi)部存儲(chǔ)

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


DateTime的內(nèi)部存儲(chǔ)格式，用十六進(jìn)制表示是：DDDDTTTT

DDDD：占用2個(gè)字節(jié)，表示對(duì)基準(zhǔn)日期的偏移量

TTTT：占用兩個(gè)字節(jié)，表示對(duì)午夜之后的始終滴答數(shù)

舉個(gè)例子，對(duì)于如下的日期和時(shí)間，把DateTime類型轉(zhuǎn)換為大小為8個(gè)字節(jié)的16進(jìn)制，每兩個(gè)數(shù)字對(duì)應(yīng)1個(gè)字節(jié)：

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

把時(shí)間的二進(jìn)制格式中的字節(jié)拆分成兩部分：前4個(gè)字節(jié)表示date，后4個(gè)字節(jié)表示time，得出的結(jié)果如下：

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，通過偏移量還原日期和時(shí)間

通過基準(zhǔn)時(shí)間和偏移量，把整數(shù)還原為原始的日期和時(shí)間：

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的內(nèi)部存儲(chǔ)

DateTime2(n)數(shù)據(jù)類型存儲(chǔ)日期和時(shí)間，它是DateTime的升級(jí)版本，由于小數(shù)秒n的精度可以自主設(shè)置，其存儲(chǔ)大小（Storage Size）不固定，DateTime2(n)占用的存儲(chǔ)空間和小數(shù)秒的精度之間的關(guān)系是：

DateTime2(n)內(nèi)部存儲(chǔ)的第一個(gè)字節(jié)存儲(chǔ)精度n，后續(xù)的字節(jié)用于存儲(chǔ)日期和時(shí)間的值。

當(dāng)小數(shù)秒的精度 n < 3 時(shí)，總的存儲(chǔ)空間是1B（精度）+6 B（數(shù)據(jù)）；

當(dāng)小數(shù)秒的精度 n 是 3 - 4 時(shí)，總的存儲(chǔ)空間是1B（精度）+ 7B（數(shù)據(jù)）；

當(dāng)小數(shù)秒的精度 n 是 5 - 7 時(shí)，總的存儲(chǔ)空間是1B（精度）+ 8B（數(shù)據(jù)），最大的小數(shù)秒精度是7，默認(rèn)值是7；

1，二進(jìn)制逆序

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

大端格式：是指數(shù)據(jù)的低位保存在內(nèi)存的高地址中，而數(shù)據(jù)的高位，保存在內(nèi)存的低地址中；

小端格式：是指數(shù)據(jù)的低位保存在內(nèi)存的低地址中，而數(shù)據(jù)的高位保存在內(nèi)存的高地址中。

舉個(gè)例子，假如內(nèi)存地址左邊是地位，右邊是高位，對(duì)于數(shù)字275，使用兩個(gè)字節(jié)來存儲(chǔ)：

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

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

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

2，DateTime2的存儲(chǔ)格式

DateTime2(n)的內(nèi)部存儲(chǔ)格式是：

第一字節(jié)存儲(chǔ)的精度n，

后三個(gè)字節(jié)記錄從基準(zhǔn)日期0001-01-01之后的多少天，采用小端格式。

中間余下的字節(jié)記錄子夜之后經(jīng)過的時(shí)間單位間隔（time unit interval，TUI）的數(shù)量，采用小端格式。

TUI是由精度來控制的，每一個(gè)TUI是10的n次方之一秒，也就是：

對(duì)于 DateTime2(7)，TUI是100ns；

對(duì)于 DateTime2(6)，TUI是1微秒（=1000ns）；

對(duì)于 DateTime2(5)，TUI是10微秒；

對(duì)于 DateTime2(4)，TUI是100微秒；

對(duì)于 DateTime2(3)，TUI是1ms（1毫秒=1000微秒）；
 
為了便于運(yùn)算，把DateTime2(n) 的字節(jié)流逆序排列：前3個(gè)字節(jié)表示的是天數(shù)，最后一個(gè)字節(jié)表示的是精度，中間余下的字節(jié)表示的TUI的數(shù)量。例如，對(duì)于 DateTime2(7)按照字節(jié)流逆序處理之后，存儲(chǔ)空間是9個(gè)字節(jié)：前三個(gè)字節(jié)是存儲(chǔ)的從基準(zhǔn)日期0001-01-01之后的多少天，最后一位是精度n，中間的5個(gè)字節(jié)表示從子夜開始有多少個(gè)TUI。

2，把DateTime2轉(zhuǎn)換為二進(jìn)制存儲(chǔ)

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

 


把二進(jìn)制值拆分成DateTime2(3)的各個(gè)組成成分：

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，利用偏移量和基準(zhǔn)還原原始值

有了偏移量，就可以在基準(zhǔn)日期和時(shí)間之上加上偏移量來獲得原始值：

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

總結(jié)
以上所述是小編給大家介紹的SQL Server 日期和時(shí)間的內(nèi)部存儲(chǔ)，希望對(duì)大家有所幫助