216
數據庫時間出現'0000/00/00',難道我穿越了？
前幾天有個朋友遇到一個問題，在做日期類型數據的運算的時候出現了‘0000-00-00’的結果，不得其解。你是否遇到過同樣的問題呢？這樣一個並不存在的時間點，難道是因為數據庫係統穿越了？
在使用ogg同步數據到備庫的時候，報ORA-01850的錯誤，通過logdump發現了很多類似的SQL報錯，選取其中一個如下：
select to_char(a.station_time) from sfis1.r_vip_log a where id=486270420;
結果顯示：0000-00-00
因為顯示結果年份為0，屬於不合法的時間格式，因此報錯。
說明：在Oracle中，date類型的數據的取值範圍是從-4712/12/31到9999/12/31之間，年份不能為0。並且從12.1開始，對於小時和分鍾做了更精確的判斷。比如來看上麵ORA-01850的錯誤為：
首先嚐試不做類型轉換，直接查詢：
select a.station_time from sfis1.r_vip_log a where id=486270420;
此時輸出時間正常。（要說明一點的是，這裏輸出的時間仍然不對，但涉及的問題跟本文無關，並且是正常的日期格式，所以此處不做深入探究）
這是什麼原因？遇到這種問題，我們可能首先會猜，是不是遭遇了bug,那麼首先來看數據庫版本，是10204，果然是比較低的版本，因此初步猜測是10g的bug。
但這一猜測很快就被否定。
為了驗證這是一個bug,Google了一下發現的確有人遭遇類似的問題，我參考一篇文章做了以下測試。（原文鏈接見：https://www.hellodba.com/reader.php?ID=95&lang=CN）
在10204，11204，12201三個版本上分別做了如下測試：
1、當日期類型做減法，剛好減完為0 的時候：
select to_date('0001-01-01', 'yyyy-mm-dd')-365 from dual;
2、減完大於0，但減法使得年份為0
select to_date('0001-01-01', 'yyyy-mm-dd')-360 from dual;
3、減完小於0
select to_date('0001/01/01', yyyy/mm/dd') - 900 from dual;
4、查詢年份為0時
select date '0000-11-22' from dual;
5、年份小於0時
select date '-4712-11-11' +15 from dual;
6、非閏年遇到2.29
select date '1500-2-28' +2 from dual;
select date '1500-2-28' +1 from dual;
7、閏年遇到2.29
select date '1600-2-28' +1 from dual;
第一次測試的結果如下：
結果並不是預期的，有很多奇奇怪怪的輸出（當時我深信他們是奇奇怪怪的），並且沒有任何一種情況出現全零。這就奇怪了，再次猜測難道那篇帖子是9i 的？
這些輸出在我看來沒有任何規律可以總結，甚至說，在10g，11g能夠正常輸出的語句在12.2中不能正常輸出而報錯的時候，我認為這是非常不科學的。
當我們認定一件事情的時候，如果這件事情不是對的，那麼很可能一直把我們帶到坑裏而不自知。很顯然之前關於版本的猜測被推翻了。因為10g中並沒有想象中出現全零的狀況，而其他的結果雖然可疑，但我並沒看出來任何規律。
柳暗花明又一村發生在不久之後。
有朋友看到我的測試結果，表達自己在11g中的測試結果不一樣。通過他發的截圖，的確又一次出現了全零的錯誤結果。
也就是說10g中很可能會全零，11g中也可能會。但為什麼我沒有遇到呢。看著兩個全零結果的截圖，突然發現兩者都是通過plsql developer連接數據庫的。這會不會有關係。此時恰好有高人指點，提到終端的顯示問題。
之後楊長老對我之前的結果做了反饋，首先對於前兩條SQL的輸出，我認為是12.2的輸出不合理，理由是做了減法反而得到的結果比被減數還大，這不科學。
事實上，對於日期類型，Oracle是可以區分正負的，正數表示公元後，負數表述公元前。隻是因為我們在常用的計算中都用到的是正數，所以默認顯示的是正數。如果需要顯示負數，需要對時間格式加以限製。
接下來我們對以上的測試SQL做逐一的分析。
對於前兩條SQL：
我們最初得到的結果如下：
很顯然，一眼看去，12.2的輸出年份不合理。但如果考慮正負的話，情況就不一樣。
我們來測試，首先修改日期顯示格式，使其能夠顯示日期類型的正負。
SQL> alter session set nls_date_format='syyyy-mm-dd hh24:mi:ss';
Session altered.
再次在12.2中執行前兩條語句，結果如下：
SQL> select to_date('0001-01-01', 'syyyy-mm-dd')-365 from dual;
TO_DATE('0001-01-01'
--------------------
-0001-01-02 00:00:00
SQL> select to_date('0001-01-01', 'syyyy-mm-dd')-360 from dual;
TO_DATE('0001-01-01'
--------------------
-0001-01-07 00:00:00
我們發現，結果為負數，也就是說，計算結果為公元前。並沒有問題，隻是之前對於時間格式的設置導致數據的正負沒有顯示出來。
第三條很好理解：本身就不存在為0的年份，因此Oracle會直接報錯。
再次查看錯誤碼，發現的確是12.1開始出現的。
結論1：在12.2中以SQLplus登錄數據庫，會識別日期類型中數據的取值範圍，而11g和10g的版本做了普通的運算而未校驗結果。
要說明一點的是，這個改變不是12.2是新特性，根據01850和01841錯誤，判斷應該是從12.1就開始有了新的校驗機製。因為我手頭沒有12.1的測試環境，有疑問的同學可以自己再驗證一下。
第四條和第五條很有意思。
從邏輯上來講，1500和1000並不屬於閏年，因此2月28加上兩天結果應該是3月2日，但此時兩個結果都很明顯算進去了不存在的2月29日。
這是怎麼回事？之前我們對於閏年，閏月，閏秒有過很多的探討，如果說Oracle不能識別那也不科學，但此時為什麼沒有顯示。
首先在楊老師建議下，我換了一組數據做了測試，將非閏年的選擇時間改為1900年。
SQL> select date '1900-2-28' +2 from dual;
DATE'1900-2-28'+2
-------------------
1900-03-02 00:00:00
SQL> select date '1900-2-28' +1 from dual;
DATE'1900-2-28'+1
-------------------
1900-03-01 00:00:00
很顯然，這次的結果考慮了閏年的問題。那說明，的確是有一個時間點的劃分，在此之前和之後，Oracle的處理方式是不一樣的。
通過以下的SQL可以驗證：
SQL> select to_date(to_char(rownum * 100) || '-2-28', 'yyyy-mm-dd') + 2 from dual connect by rownum <= 30;
TO_DATE(TO_CHAR(ROWN
--------------------
0100-03-01 00:00:00
0200-03-01 00:00:00
0300-03-01 00:00:00
0400-03-01 00:00:00
0500-03-01 00:00:00
0600-03-01 00:00:00
0700-03-01 00:00:00
0800-03-01 00:00:00
0900-03-01 00:00:00
1000-03-01 00:00:00
1100-03-01 00:00:00
1200-03-01 00:00:00
1300-03-01 00:00:00
1400-03-01 00:00:00
1500-03-01 00:00:00
1600-03-01 00:00:00
1700-03-02 00:00:00
1800-03-02 00:00:00
1900-03-02 00:00:00
2000-03-01 00:00:00
2100-03-02 00:00:00
2200-03-02 00:00:00
2300-03-02 00:00:00
2400-03-01 00:00:00
2500-03-02 00:00:00
2600-03-02 00:00:00
2700-03-02 00:00:00
2800-03-01 00:00:00
2900-03-02 00:00:00
3000-03-02 00:00:00
我們看到，對於整百的非閏年年份，做相同的計算，在1600年前後的結果是不一樣的。1600之後，才開始識別到底有沒有2月29這個日期，之前的年份不做校驗。
為什麼會這樣？
這裏跟大家科普一下：
公曆是根據羅馬人的"儒略曆"改編而得。由於當時沒有了解到每年要多算出0.0078天的問題，從公元前46年，到16世紀，一共累計多出了10天。為此，當時的教皇格列高利十三世，將1582年10月5日人為規定為10月15日。並開始了新閏年規定。即規定公曆年份是整百數的，必須是400的倍數才是閏年，不是400的倍數的就是平年。
簡單來說，就是1582之後才有閏年計算標準，因此在上麵的測試中，由於1600是閏年，從1700開始的結果是正確的。
結論二：由於閏年的計算標準始於1582年，Oracle對於這個時間點前後的數據處理方式不一樣，1582之前的數據，不做閏年校驗，默認2月29天；1582之後才開始校驗。
當然Oracle在這裏也是有點偷懶，認為在1582之前沒有這種計算標準，就不做校驗是不對的。
接下來的兩條SQL，
對於年份小於0的，或者運算之後的年份小於0的，同樣是負數，也就是公元前的計時方式。我們通過顯示正負號再次驗證：
SQL> alter session set nls_date_format='syyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select to_date('0001/01/01','syyyy/mm/dd') - 900 from dual;
TO_DATE('0001/01/01'
--------------------
-0002-07-16 00:00:00
SQL> select date'-4712-11-11'+15 from dual;
DATE'-4712-11-11'+15
--------------------
-4712-11-26 00:00:00
全為負值，也就是公元前，經計算，邏輯合理。
至此，關於日期的正負告一段落。那麼，為什麼有些環境下能測出全零的結果呢？
之前我們說，可能是終端的顯示問題。事實上，更確切的說，不隻是顯示，更是工具與Oracle在進行銜接過程中的機製問題。
通過plsql developer等工具連接數據庫的時候，事實上並不隻是簡單地連接，讀取請求和返回結果，內部有很多複雜的機製。Oracle是用c語言寫的，為了跟Oracle內部更好地銜接，這些外部的工具很可能做了很多c的接口，這些接口對於數據的操作不止於讀取和傳遞，這就是為什麼很多時候我們使用不同的工具很可能同樣的SQL會有不一樣的結果。
回到最初的問題：
select to_char(a.station_time) from sfis1.r_vip_log a where id=486270420;
使用to_char函數做轉換的時候，導致出現全零，也可以看出在plsql developer上調用to_char函數的時候，對於數據格式和類型的轉換機製不完全等同於Oracle的轉換機製。這很可能也是因為工具的接口函數導致。
這樣的例子還有很多，比如當我們用SQLplus連接數據庫查詢一張表的時候，會直接返回所有的行，但plsql developer則可能會先顯示一部分，其他的你可以根據需求展開。也有在做邏輯導入導出的時候，有一些表和索引的創建出現問題，直接在SQLplus執行出錯，而使用plsql developer則可以。
具體的區別如果有人感興趣，可以做更深入的學習。我們在這裏想跟大家強調的是，當你使用第三方工具操作數據庫的時候，千萬要謹慎，我們並不知道這些工具裏到底埋了多少坑。
所以，至此你是否也有豁然開朗的感覺？
最後，關於文中提到的黃瑋老師的帖子，猜想很可能是比較早期的版本，或者當時的一些工具的不完善導致。當然歸根到底，我們看到Oracle在12c之前，通過運算仍然可以得到年份為0的情況，雖然正常顯示了，但由於是不合法的數據，這些數據在需要使用比如導入導出，計算或者轉換的時候就可能出錯。
Oracle一直在不斷地完善和進步，關於12c的新特性，也仍有很多待我們去發現。熱愛學習的你，歡迎跟我們一起來踩坑。
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-17 17:03:05