PLSQL_基础系列04_时间间隔INTERVAL(案例)

2014-12-08 Created By 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再看该文.

 

  1. 范围值:

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

 

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

表示: 30.1235秒,
因为该地方秒的后精度设置也4, 要进行四放弃五合乎

 

  1. 其他

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

相关文章