某天突然收到隔壁部門人員詢問，SQL 日期欄位的 -0.5 / 24 為何意?
據說是來自合作單位的操作，可以取得當前時間30分鐘內的資料。
平常對日期時間的操作並不陌生，多還是使用SQL公式，
第一想法是將日期數字化的操作，但第一次看到這神秘公式的用法還是滿好奇的。
日期時間 -0.5 / 24
網路上相關的使用案例還不少，只是不太找到詳細的說明。測試後得到其規則如下 :
基本單位為「天」，也就是說沒有分母時，直接減去的數值是影響「日期」的部分。
若欲增減的單位為「時」、「分」，則同等換算，將分母分別乘上 24 或 24 × 60 (依數學原理要記得括號)。單位為「秒」時以此類推。
分子則為欲更動單位數，可理解為下圖。
怎麼算都一樣? 目標時間未更動
可能會有些人注意到，為什麼只有「天」的分子是寫 1 而非 1.0。
將上述相同的 SQL 中， 1.0 都改為 1，再看看執行結果。
可以發現除了「天」及「月」以外，「時」、「分」、「秒」都未得到預期結果，得到結果與目標時間相同。
開頭可能會感到莫名其妙，其實是源於熟悉的基本原理。在許多程式語言和 SQL中，關於整數和小數的運算會根據資料類型及其運算方式產生不同的結果。「時」、「分」、「秒」基本都會是浮點運算，若分子分母都為整數時，則截斷小數部分。以上述例子得到結果0，因此當然對原有日期的運算沒有影響，要稍加留心。
保險起見將分母皆寫為浮點型別 ( 分子亦可 ) ，則可避免此情況。
毫秒誤差值? 分母盡量寫為 公式 而非 結果
前面範例為方便做比較，以 CAST 函式統一顯示格式為 DateTime2(0) 。然改為 DateTime2 格式時，可以看到結束有些出乎意料。
結果如圖中所示，分母若寫為計算過的結果，在毫秒上會有稍微誤差，例如 : 24 要寫成 1 * 24 、分的話要寫成 24 * 60 而非直接寫 1440 。
分母大於1的，都要寫成運算式會比較精準。
日期轉數字格式
撰寫程式時型別轉換是常有的事，只是比較少會遇到將日期數字化。反而有時在 Excel 處理資料時會遇到。簡單來說就是將日期變成數日子的概念，每過一天便增加1，年月時分秒各別按比例計算。乍看下類似於天文學家所使用的「儒略日」記法。
應用的部分基本如上所述，由公式可看出原理。現在轉為數字來實際驗證看看。( 數學系會不會覺得用不著算，上面數字看著就知道了? ( 笑
一樣目標時間為 2024 年 02 月 01 日 15 點整，與 -0.5 / 24 的 2024 年 02 月 01 日 14 點 30 分為例。而後將兩者轉為數字，可得到結果如下 :
可發現其相差剛好為0.02083，幾同 -0.5 ÷ 24 得 -0.020833 。
以 Excel 測試的話，直接輸入日期後將其改為數字格式其可。
同樣的日期，為什麼SQL跟Excel得到的數值不同?
實際到 Excel 操作一次以上的範例，同樣將 2024 年 02 月 01 日 15 點整，與 2024 年 02 月 01 日 14 點 30 分轉為數值
從其計算可看出相差仍同 -0.5 ÷ 24 所得的 -0.02083。
或許有人注意到，同樣的日期在 SQL 所得的數值為 45321.62500 及 45321.60417，與 Excel 所得相差為 2 的差異。
其來自起始數值及對閏年的計算，SQL的基準日由0，即1900年的01月01日開始起算；同樣日期在 Excel 所得的數值為 1。
而除了起始數值的差異外，另一個差異為對閏年的計算。
普遍大家所知多為四年一閏，然在歷史長久的演變中，其實曆法的計算是非常複雜的 ( 有興趣者可參閱，有挺詳細的說明 )
所以第二個差異便是發生在 1900 年的閏年計算，依照規則 1900 年沒有閏日為平年。
因此在 SQL 的轉換會出現錯誤 : 「The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.」
然而該日期在 Excel 是可以轉換，並得到結果為 60。由該日之後的日期，如 : 1900 / 3 / 1 便開始跟 SQL 所得數值相差為 2
結語
相信經驗豐富的開發者在看到這些公式與數值時應該都能理解，或至少感到熟悉，稍加推測就可得知結果。
只是在可讀性與精確性上，還是較為推薦直接使用 dateadd 或 datediff 函式做計算，至少一目了然。
或若有人知道此寫法的優點，歡迎留言分享~
而 dateadd 函式在網路上的資料十分充足，這裡就不再多做贅述。
若是哪天有遇到相關的疑難雜症再做補充。
參考資料
如何用Sql语句获取当前时间，并且在当前时间的基础上减去30分钟
how to fetch the half hour records if the time comes dynamically
【程式學習之路：Day21】關聯式資料庫應用：日期與時間處理、T-SQL
留言列表
{{ article.title }}