Oracle日期型別操作幾個問題

日期型別Date是Oracle的一個資料型別，將日期和時間以數字型別儲存在資料庫中的格式。

 

在官方文件（11gR1）中，對於Date型別資料描述如下：

 

“Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.It does not have fractional seconds or a time zone.”

 

這部分告訴我們Oracle的Date型別資料是表示自公元前4712年1月1日（也就是Julian Days，一種曆法日期）到公園9999年1月1日。本質上應該是一種數字型別計數。Date型別的顯示是受到Oracle系統引數nls_date_formate和nls_territory控制，根據不同的地區、語言，可以顯示成不同的格式。儲存容量上，Date型別佔用7個位元組大小。包括年、月、日、小時、分鐘和秒。下面根據幾個容易出現的問題進行分析：

 

1、  Date型別資料增減

這部分參考了eygle老師的一篇博文。

Date型別一個好處是可以直接進行日期的增減操作，透過加減符號就可以實現。如下例：

 

先設定日期顯示格式

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

Session altered

 

處理加減

 

SQL> col now format a20;

SQL> col next_hour format a20;

SQL> col next_minute format a20;

SQL> col next_second format a20;

SQL> select sysdate as now, sysdate+1/24 as next_hour, sysdate+1/(24*60) as next_minute, sysdate+1/(24*60*60) as next_second from dual;

 

NOW          NEXT_HOUR        NEXT_MINUTE       NEXT_SECOND

-------------------- -------------------- -------------------- --------------------

2010-12-2 22:39:24   2010-12-2 23:39:24   2010-12-2 22:40:24   2010-12-2 22:39:25

 

上面可見，對日期型別資料進行加減，可以方便的進行日期推進和後退。標準是將一天作為整數1，其他如年、月、小時、分、秒都是依次放大或者縮小相應的倍數。如要想獲得當前時間3分鐘後的時間，就需要sysdate+3/(24*60)。這樣的操作在實際中效率很高。

 

 

2、  日期型別引數的設定

日期的格式是顯示日期的重要內容。但是有一點需要注意，日期的顯示是與Oracle設定的日期格式、語言和地區相關的，但是本質上Date型別儲存的數值是固定不變的。

 

引數nls_time_format可以直接對於日期格式的現實進行控制，如上述程式碼示例中顯示的內容。此外，nls_territory和nls_date_language在一定程度上也會影響到日期格式的顯示。透過檢視v$nls_parameters，可以檢視到預期有關的引數。

 

SQL>  select * from V$NLS_PARAMETERS;

 

PARAMETER                                       VALUE

----------------------------------------------------------------  ---------------------------------

NLS_LANGUAGE                                                     SIMPLIFIED CHINESE

NLS_TERRITORY                                                    CHINA

NLS_CALENDAR                                                     GREGORIAN

NLS_DATE_FORMAT                                                  DD-MON-RR

NLS_DATE_LANGUAGE                               SIMPLIFIED CHINESE

NLS_TIME_FORMAT                              HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM

……

 

19 rows selected

說明：上述程式碼省略了部分結果。

 

三個引數都可以影響到日期輸出的格式。實際中，無論是直接書寫伺服器端程式碼，還是在應用中書寫，最好都直接指定出轉換日期格式。在進行資料庫移植的時候，也要注意不同語言、日期格式的資料庫進行轉移時，要顯示進行資料格式的設定。

三個引數中，比較常用的是nls_date_formate和nls_date_language。一般如果在session進行格式規範，可以如下：

 

SQL> alter session set NLS_DATE_LANGUAGE = 'SIMPLIFIED CHINESE';

Session altered

 

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24-MI-SS';

Session altered

 

 

3、“J”格式符

在對Date型別轉換為字元型別的時候，有一些程式碼中使用到了“J”。如下：

 

SQL> select to_char(sysdate,'J') from dual;

 

TO_CHAR(SYSDATE,'J')

--------------------

2455533

 

J實際表示的Julian Days，返回的就是該日期與Julian Days之間的天的數量差。

 

 

4、  進行日期範圍查詢

Date型別除了包括年月日資訊之外，還包括時間精確的資訊。對於一些應用而言，這部分時間精確資訊反而成為了一種負擔。有時候，也因此避免選擇Date作為日期資訊型別，而去選擇Number型別作為替代。

 

當進行日期查詢時，就會存在一些問題。如下：

 

構造資料表tt。

 

SQL> create table tt as select object_id, created from dba_objects where 1=0;

 

Table created

SQL> insert into tt select rownum, sysdate from dba_objects where rownum<4;

 

3 rows inserted

SQL> commit;

 

Commit complete

 

搜尋與今天相同日期的方法。

SQL> select * from tt where created = to_date (to_char (sysdate, 'yyyy-mm –dd ' ),'yyyy-mm-dd');

 

 OBJECT_ID CREATED

---------- -----------

 

 

查詢不到資料的原因很簡單。因為插入資料的時候，sysdate是攜帶著時間資訊的，如果簡單按照天來進行查詢，是沒有結果。解決的方法有下面幾個，各有利弊：

 

方法一：在查詢欄位下手處理，也是最簡單的方法。

SQL> select * from tt where to_char(created,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');

 

 OBJECT_ID CREATED

---------- -----------

         1 2010-12-2 2

         2 2010-12-2 2

         3 2010-12-2 2

 

首先，這樣做可以獲取到正確的結果。而且語句的複雜程度也可以接受。缺點是在欄位加入了函式操作，一般在created上的索引是無法其效果的。當然，這個問題也可以透過函式索引來解決，在created欄位上加一個to_char()操作的索引。

SQL> create index ind_tt on tt(to_char(created,'yyyy-mm-dd'));

 

Index created

 

方法二：使用範圍查詢代替等於查詢。

不在搜尋欄位上進行處理，轉化為搜尋全天時間範圍。

 

SQL> select * from tt where created>=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and created < to_date (to_char(sysdate+1,' yyyy-mm-dd'),'yyyy-mm-dd');

 

 OBJECT_ID CREATED

---------- -----------

         1 2010-12-2 2

         2 2010-12-2 2

         3 2010-12-2 2

 

這種方法可以便於利用索引，也容易在日期範圍查詢功能上覆用，缺點是複雜度略高。

 

 

結論：日期Date是Oracle資料庫中一種常見的資料型別。無論是儲存效率還是操作上，都已經是比較成熟的技術型別。本文只是針對幾個常見問題進行討論說明，其他如各種日期函式使用、間隔Interval型別使用等問題，留待日後繼續討論。