在工作中總是會碰到各種和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
[oracle@iZ23snm97y9Z ~]$ date
2015年 4月 1日 水曜日 11:15:04 CST
[oracle@iZ23snm97y9Z ~]$ export LANG=en_US.UTF-8
[oracle@iZ23snm97y9Z ~]$ date
Wed Apr 1 11:15:23 CST 2015
LANG是針對Linux系統的語言、地區、字元集的設定，對linux下的應用程式有效，如date；NLS_LANG是針對Oracle語言、地區、字元集的設定，對oracle中的工具有效
這個問題的模拟，還是需要模拟用戶端程式來做了，關于日期的一些格式化設定，目前還是需要通過分析一下JDBC中的一些細節來看看有什麼細節之處了。
最後還是感謝木呼提供的精彩案例。