艾銻知識 | SQL Server 日期和時間的內部存儲過程

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

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


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

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

艾銻知識 |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個字節：

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

把時間的二進制格式中的字節拆分成兩部分：前4個字節表示date，后4個字節表示time，得出的結果如下：

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

通過基準時間和偏移量，把整數還原為原始的日期和時間：

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的內部存儲

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。

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

 


把二進制值拆分成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，利用偏移量和基準還原原始值

有了偏移量，就可以在基準日期和時間之上加上偏移量來獲得原始值：

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

總結
以上所述是小編給大家介紹的SQL Server 日期和時間的內部存儲，希望對大家有所幫助