2014-12-08 BaoXinjian

一、摘要

INTERVAL数据类型用来存储两个时间戳之间的时间间隔。

可以指定years and months，或者days，hours，minuts，seconds之间的间隔。

ORACLE支持两种INTEVAL类型，它们分别是YEAR TO MONTH和DAY TO SECOND。

每个类型都包含leading field和trailing field。主参数定义要被计算的date或者time，副参数定义最小增长量

二、语法

1. Oracle语法

INTERVAL ‘{ integer | integer time_expr | time_expr }‘

{ { DAY | HOUR | MINUTE } [ ( leading_precision ) ]

| SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }

[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]

(1). leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.

(2). 该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.

2. 范围值:

HOUR:    0 to 23

MINUTE: 0 to 59

SECOND: 0 to 59.999999999

三、案例分析

1. INTERVAL ‘400 5‘ DAY(3) TO HOUR

表示: 400天5小时, 400为3为精度,所以"DAY(3)", 注意默认值为2

2. INTERVAL ‘30.12345‘ SECOND(2,4)     

表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入

3. 其他

select date ‘2010-01-01‘ + interval ‘21‘ year from dual

select date ‘2010-01-01‘ + interval ‘123-2‘ year(4) to month from dual

select date ‘2010-01-01‘ + interval ‘100‘ month from dual

select date ‘2010-01-01‘ + interval ‘100‘ day(2) from dual

select date ‘2010-01-01‘ + interval ‘1 2‘ day to hour from dual

select date ‘2010-01-01‘ + interval ‘1 2:3‘ day to minute from dual

select date ‘2010-01-01‘ + interval ‘1 2:3:4‘ day to second from dual

select date ‘2010-01-01‘ + interval ‘1‘ hour from dual

select date ‘2010-01-01‘ + interval ‘1:2‘ hour to minute from dual

select date ‘2010-01-01‘ + interval ‘1:2:3‘ hour to second from dual

select date ‘2010-01-01‘ + interval ‘1:2‘ minute to second from dual

Thanks and Regards

PLSQL_基础系列4_时间间隔INTERVAL