在工作中總是會碰到各種和date相關的問題,一般這種問題都是讓人很糾結的。
比如前幾天一個朋友和我分享了他關於時間問題的兩個案例。
第一個是他在做impdp導入數據的時候，發現導入很順利完成了，但是原本的timestamp數據類型的數據都變成了sysdate值了，這樣數據就完全不對應了。timestamp的數據類型精度要高得多，可以精確到小數秒（默認是6位，算是微妙了，可以最高到9位），可能在有些系統中精確到秒已經足夠了，但是據我所知，還沒有這種數據類型的強制轉換。我們聊了會，基本的共識就是資料庫層面不會自動做這種數據類型的轉換，過了會他告訴我，發現問題了，是由於觸發器導致的。
觸發器的內容大體如下：
CREATE OR REPLACE TRIGGER "XXXXXX_TRG" BEFORE INSERT OR UPDATE ON XXXXXX_HISTORY REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN IF (INSERTING) THEN :NEW.ADD_TIMESTAMP := SYSTIMESTAMP; END IF; :NEW.REC_TIMESTAMP := SYSTIMESTAMP; END;
有了這些信息，也算是虛驚一場。
不過觸發器帶來的問題真是無所不在，我之前在數據遷移的時候碰到一個問題，也是在數據導入的時候沒有禁用觸發器，結果數據導入之後，本來需要導入1500萬數據的表，結果在數據導入之後發現導入了1800萬，多了300萬的數據，當時也是感覺很蹊蹺，最後排查發現是由於觸發器導致的。最後也是經過反覆確認，準備了一堆的腳本最終清理了那多餘的300萬數據。也算是沉痛的教訓，所以在數據導入的過程中，觸發器的作用是顯而易見的，有時候我們需要禁用，有時候需要啟用，得看業務需要了。
過了幾天，這個朋友碰到了另外一個問題，問題的場景更為複雜，是一個日本開發的系統做遷移(包括應用代碼遷移),通過客戶端程序調用的時候發生了數據類型的衝突。
朋友在他們的應用程式中的代碼查到他們的SQL代碼。如下格式:
select * from emp where hiredate >= to_char(sysdate,'YYYY-MM-DD');
他們在sql中用date 型數據與 char型的數據做了一個比較。
大家都知道，這種寫法是有錯誤的，一般會報一個 ORA-01861: 文字與格式字符串不匹配
但是這生產環境已經運行了很長時間，一直沒有報錯，原因是為什麼呢？
大家都知道 NLS_DATE_FORMAT這個參數，也知道有LANG這個環境變量
也知道session>instance>database;
但是是否有想過session中這個 NLS_DATE_FORMAT 是根據什麼去判定的？是session 發起端的 date_format，還是 db端響應的date_foramt.
date_format 與語言環境又有沒有關係？
一般來說能夠修改NLS_DATE_FORMAT的方式有以下幾種
第一種是通過系統變量NLS_DATE_FORMAT,這個變量也是依賴於NLS_LANG的設置的。
第二種是通過sqlplus的glogin.sql來設置NLS_DATE_FORMAT，alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';這個也算是統一的配置了。
第三種是通過session級的變更來完成，直接作用於當前的session.alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
第四鍾是通過system級的變更，直接通過alter system
set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';這個作用範圍很廣，一般是不需要這麼做的。
這幾種方式貌似都和他碰到的問題不搭邊，最後他是通過logon觸發器來搞定的。可以設定一些規則，做一些針對性的操作。
觸發器的內容如下：
CREATE OR REPLACE TRIGGER LOGINTRG
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''yyyy-mm-dd hh24:mi:ss''';
END LOGINTRG;
這種方式確實是奏效了，但是解決了問題但是心裡還是感覺不夠踏實，因為這種一種特殊的處理。
最後他們經過很多的分析，
原本的設置是英文，發現修改客戶端的LANG參數配置為日文就不需要觸發器了，這個問題就引刃而解了。
我們先從LANG這個環境變量入手
大家都知道
LANG變量是language的簡稱，比如這幾種配置
LANG=zh_CN.UTF-8
LANG=en_US.UTF-8
LANG=ja_JP.UTF-8
下面是三個語言環境的日期格式
[oracle@iZ23snm97y9Z ~]$ export LANG=zh_CN.UTF-8
[oracle@iZ23snm97y9Z ~]$ date
2015年 04月 01日 星期三 11:14:50 CST
[oracle@iZ23snm97y9Z ~]$ export LANG=ja_JP.UTF-8
2015年 4月 1日 水曜日 11:15:04 CST
[oracle@iZ23snm97y9Z ~]$ export LANG=en_US.UTF-8
Wed Apr 1 11:15:23 CST 20
15
LANG是針對Linux系統的語言、地區、字符集的設置，對linux下的應用程式有效，如date；NLS_LANG是針對Oracle語言、地區、字符集的設置，對oracle中的工具有效
這個問題的模擬，還是需要模擬客戶端程序來做了，關於日期的一些格式化設置，目前還是需要通過分析一下JDBC中的一些細節來看看有什麼細節之處了。
最後還是感謝木呼提供的精彩案例。
