Timestamp型別淺析

 

Oracle針對不同的資料需求，提供了多種類、多層次的資料型別體系。我們在實際應用中，最好可以依據業務資料的實際形態和前端應用的語言、框架特性來確定欄位型別的選擇。

 

 

Date型別是我們經常使用的時間型別資料表示，包括了年月日時分秒資訊。作為Date型別的一個擴充，Oracle提供了Timestamp資料型別，作為高精度時間型別的體現。

 

 

1、Timestamp的高精度

 

Timestamp在官方中的定義方式是timestamp(n)，其中n表示秒級片段（fractional_seconds）的精確度。作為Date型別的一個擴充，Timestamp提供了更為精確的時間定位。

 

 

SQL> select systimestamp from dual;

 

SYSTIMESTAMP

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

13-7月 -11 07.52.16.562000 下午 +08:00

 

SQL> select sysdate from dual;

SYSDATE

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

2011-7-13 20:13:55

 

 

使用systimestamp函式，我們可以獲取到當前時間的timestamp with local time zone取值。預設情況下，我們發現和sysdate顯示的有一些差異。控制和顯示timestamp格式的引數，可以透過v$nls_parameters進行檢視。

 

 

SQL> select * from v$nls_parameters;

 

PARAMETER                      VALUE

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

（篇幅原因，有省略…）

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

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

NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

 

19 rows selected

 

 

引數nls_timestamp_format和nls_timestamp_tz_format分別來控制timestamp和timestamp with local zone的顯示格式。

 

上面systimestamp顯示的資訊，可以解析為：2011-7-13 19:52:16。稍稍費解的是秒後面的562000數字。這個是Timestamp的秒片段（fractional seconds），就是秒向下的精度劃分。timestamp定義中的n就是指定的每秒劃分的精度範圍。

 

實際應用中，n取值為0-9，預設為6。從上面例子上的systimestamp函式返回值上看，也是的確如此。

 

 

SQL> create table t (d1 timestamp, d2 timestamp(0), d3 timestamp(9));

Table created

 

SQL> desc t;

Name Type         Nullable Default Comments

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

D1   TIMESTAMP(6) Y                        

D2   TIMESTAMP(0) Y                        

D3   TIMESTAMP(9) Y                        

 

 

那麼，timestamp型別的高精度有什麼作用呢？個人認為大部分還是為了前端併發訪問時候的鎖機制，還有一些特殊行業的高精度需求。很多開發框架都需要使用一個高精度的資料列來標記行訪問的時間順序，透過timestamp的精度，基本可以滿足這部分的需要。

 

2、Timestamp with time zone

 

Timestamp型別的另一個特性就是時區timezone特性。我們知道世界各地所處的時區不同，對應同一個時刻顯示的時間是不同的。所以如果精確定義一個時間點，特別是對應一個全球性系統的時候，時區Timezone是不可或缺的考慮因素。

 

Date型別只能適應於一般時間的表示，如果加入了時區因素，就無能為力了。在這樣的情況下，就可以選擇Timestamp型別的一個擴充型別，timestamp with time zone。這樣就可以把當前對應的時區資訊加入到資料列中。

 

函式systimestamp返回標準型別就是timestamp with time zone。與一般timestamp型別不同的是，timestamp with time zone額外加入了當前時區的資訊。

 

 

SQL> select systimestamp from dual;

 

SYSTIMESTAMP

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

13-7月 -11 07.52.16.562000 下午 +08:00

 

 

+08:00表示時區，全球有12個時區。系統的時區透過引數設定或者當前作業系統OS的配置來獲取。

 

 

SQL> create table t (d1 date, d2 timestamp, d3 timestamp with time zone);

Table created

 

SQL> insert into t values (systimestamp, systimestamp, systimestamp);

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> select * from t;

 

D1                   D2                                       D3

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

2011-7-13 21:12:32   13-7月 -11 09.12.32.859000 下午          13-7月 -11 09.12.32.859000 下午 +08:00

 

 

加入時區資訊之後，可以做到全球時間下的絕對時間比較。

 

此外，timestamp還有一個timestamp with local time zone的擴充型別。也包括時區資訊，與time zone的差別有下面幾個：

 

ü        不管是什麼時區的日期插入到資料庫中，都會進行正規化工作。將其他時區的日期轉化為資料庫對應的時區（Database Time Zone）中；

ü        當進行檢索操作的時候，local time zone型別資料又會被轉化為當前檢索會話對應的time zone中。這一系列的轉化操作對end user而言都是透明的；

 

在對應會話和資料庫的時區上，與系統引數NLS_TERRITORY是相關的。Oracle往往透過這個引數的國家設定來判斷時區。而客戶端的引數往往與nls_lang相關。

 

 

3、Timestamp顯示格式控制

 

最後我們一起來看看timestamp顯示的格式方式。預設情況下，timestamp以及timestamp with time zone的顯示是與系統引數nls_timestamp_format和nls_timestamp_tz_format有關。在我們的實驗環境下，兩個格式字串（format string）分別為：

 

 

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

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

 

 

其中，DD、MON、HH和MI都是我們非常熟悉的日期格式字串。RR表示使用年的後兩位進行表示。而SSXFF是一個組合條件，應該被拆分為SS、X和FF三個部分。SS表示秒資訊，X表示基數開關，而FF表示小於一秒的時間片段。

 

X表示的基數開關是什麼意思呢？我們透過實驗就可以看出情況。

 

 

SQL> select to_char(systimestamp,'DD-MON-RR HH.MI.SSXFF AM') SSXFF, to_char(systimestamp,'DD-MON-RR HH.MI.SSFF AM') SSFF from dual;

 

SSXFF                                  SSFF

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

13-7月 -11 09.37.16.843000 下午        13-7月 -11 09.37.16843000 下午

 

 

加入了X表示開關之後，就在秒為加入了一個.號表示分割。便於檢視。

 

而AM表示是否使用（AM和PM）顯示上下午資訊，TZR則表示是否顯示出時區資訊。

 

當顯示timestamp型別的時候，如果沒有明確指定顯示格式，系統會自動使用nls_timestamp_format系列引數。

 

SQL> select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss ff tzr') from dual;

 

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-

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

2011-07-13 21:40:50 265000 +08:00

 

 

如果期望在會話（或者系統）一級修改引數，就要使用alter session或者alter system方法。

 

 

SQL> select to_char(systimestamp) from dual;

 

TO_CHAR(SYSTIMESTAMP)

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

13-7月 -11 09.42.44.343000 下午 +08:00

 

//對當前會話修改引數；

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

Session altered

 

SQL> select to_char(systimestamp) from dual;

 

TO_CHAR(SYSTIMESTAMP)

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

2011-07-13 21:43:18 984000 +08:00

 

 

4、結論

 

timestamp系列型別是對Oracle Date型別的一種有益補充。在需要的場合下，可以適當使用。