如何掌握 Oracle 中的時間間隔型資料

在9i 版本以前，Oracle 沒有內建的方式來記錄時間的流逝。DATE型資料用來記錄單獨的時間點；但是要表達一個時間量（也就是一個間隔），資料庫的設計者就必須把時間間隔轉換成原始單位秒，然後用一個NUMBER列來儲存它。

雖然NUMBER這個資料類型可以以秒為單位準確地表示時間，但是它使得時間的計算變得很困難。比如，60秒是1分鐘，60分鐘是1個小時，24個小時等於1天——這些數字在以十進位為基礎的數字系統中都是非常蹩腳的。

在Oracle 9i中，按照SQL 99標準，增加了時間間隔型資料INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND，它們和其他幾種資料類型一起使得對時間的處理更加準確。TIMESTAMP、TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE等資料類型都把時間的表達精確到了若干分之一秒，而且後面兩種還解決了地理位置造成的時間變化。

在SQL和PL/SQL中，你都可以用時間間隔型資料，它們都是用同一種方式規定的：

INTERVAL YEAR[(year_precision)] TO MONTH

INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

對於精確數值，規定有預設值：年和日是兩位元，若干分之一秒是六位元。

時間間隔的大小由INTERVAL來表示，後面緊接一個放在單引號中的運算式，以及用來解釋該運算式的文字。用YEAR TO MONTH表示時間間隔大小時要在年和月之間用一個連字號(-) 串連。而DAY TO SECOND表示時間間隔大小時要在日和時間之間用一個空格串連。舉個例子來說，下面是2年6個月的時間間隔的表示方法：

INTERVAL '2-6' YEAR TO MONTH

下面的例子表示3天12個小時30分鐘6.7秒：

INTERVAL '3 12:30:06.7' DAY TO SECOND(1)

時間間隔可以為正，也可以為負。它們可以從各種TIMESTAMP資料類型中加上或者減去，從而得到一個新的TIMESTAMP資料類型。它們之間也可以做加減運算得到新的時間間隔。

列表A說明了怎樣建立一個表格來記錄一個事件的開始時間和期間，如實驗等。資料被收集以後，SQL中內建的摘要函數不需要與原始單位秒進行相互轉換，就可以報告總的期間和平均持續時間。

列表A

CREATE TABLE experiment

(experiment_id NUMBER(9),

experiment_desc VARCHAR2(80),

experiment_start TIMESTAMP,

experiment_duration INTERVAL DAY(1) TO SECOND(4)

);

Table created.

INSERT INTO experiment

VALUES (

1, 'Busted urban myth', '01-JUN-2006 02:00:00 PM',

INTERVAL '1 2:31:15.1250' DAY(1) TO SECOND(4)

);

1 row created.

col experiment_desc format a40

col experiment_start format a30

col experiment_duration format a20

SELECT * FROM experiment;

EXPERIMENT_ID EXPERIMENT_DESC ------------- ---------------------------------------- EXPERIMENT_START EXPERIMENT_DURATION ------------------------------ -------------------- 1 Busted urban myth 01-JUN-06 02.00.00.000000 PM +1 02:31:15.1250

-- Now compute the experiment's ending time

SELECT experiment_id, experiment_start,

experiment_start + experiment_durationexperiment_end

FROM experiment;

EXPERIMENT_ID EXPERIMENT_START ------------- ------------------------------ EXPERIMENT_END --------------------------------------------------------------------------- 1 01-JUN-06 02.00.00.000000 PM 02-JUN-06 04.31.15.125000000 PM

但遺憾的是， TO_CHAR函數中沒有包括任何能夠映射到各個時間間隔資料類型片段的格式模型。但是，你可以用新的EXTRACT函數來提取和合并這些片段。格式如下：

EXTRACT(timepart FROM interval_expression)

列表B給出了一個運用這種方法的例子。

列表B

SELECT EXTRACT(DAY FROM experiment_duration) ||

' days, ' || EXTRACT (HOUR FROM experiment_duration) ||

' hours, ' || EXTRACT (MINUTE FROM experiment_duration) ||

' minutes' Duration

FROM experiment;

DURATION

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

1 days, 2 hours, 31 minutes

首先，從experiment_duration列中將天數提取出來，文字“Days”是與之相聯的。對於實驗期間中的小時和分鐘部分，操作與上述方法一樣。