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

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

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


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

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

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


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

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

把時間的二進(jìn)制格式中的字節(jié)拆分成兩部分：前4個字節(jié)表示date，后4個字節(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，通過偏移量還原日期和時間

通過基準(zhǔn)時間和偏移量，把整數(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)部存儲

DateTime2(n)數(shù)據(jù)類型存儲日期和時間，它是DateTime的升級版本，由于小數(shù)秒n的精度可以自主設(shè)置，其存儲大小（Storage 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。

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)的各個組成成分：

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)日期和時間之上加上偏移量來獲得原始值：

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 日期和時間的內(nèi)部存儲，希望對大家有所幫助