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

文章作者 100test 发表时间 2007:03:14 13:46:57
来源 100Test.Com百考试题网

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