如何掌握 Oracle 中的时间间隔型数据 （2）

 来源：赛迪网技术社区    作者：hyztxeasy

列表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”是与之相联的。对于实验持续时间中的小时和分钟部分，操作与上述方法一样。