oracle中的INTERVAL函数用法

oracle中的INTERVAL函数用法

一、interval year to month 类型

格式：
该数据类型常用来表示一段时间差, 注意时间差只精确到年和月， precision为年或月的精确域, 有效范围是0到9, 默认值为2

[ ] 为可选项
|  表示任选其中一项
INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]

案例1：
表示123年2个月。“YEAR(3)” 表示年的精度为3, 如果YEAR(n), n<3就会报错，ORA-01873间隔的前导精度太小（注意默认是2）

select INTERVAL '123-2' YEAR(3) TO MONTH from dual;   +123-02

案例2：
如果前面没有写减号，可以省略后面的’TO {YEAR | MONTH}’，如果要写后面的必须跟前面的统一

select INTERVAL '12' YEAR from dual;   +12-00 --表示12年
select INTERVAL '12' YEAR TO YEAR from dual;   +12-00 --表示12年
select INTERVAL '9' MONTH from dual;   +00-09 --表示9个月
select INTERVAL '9' MONTH TO MONTH from dual;   +00-09 --表示9个月

案例3：
表示12个月，一年12月，自动换算成1年

select INTERVAL '12' MONTH from dual;   +01-00

案例4：
YEAR和MONTH的默认精度为2，MONTH 的精度是换算成年来看的

select INTERVAL '1200' MONTH from dual;   --ORA-01873间隔的前导精度太小
select INTERVAL '1200' MONTH(3) TO MONTH from dual;   +100-00
select INTERVAL '1199' MONTH from dual;   +99-11

案例5：

select INTERVAL '5-11' YEAR TO MONTH + INTERVAL '20' MONTH from dual;   +07-07 --表示 5年11个月+20个月=7年7个月 
select sysdate - (INTERVAL '1' MONTH) from dual;    2021/2/9 13:14:44 --表示当前时间前一个月时间 

二、interval day to second 类型

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

• HOUR: 0 to 23
• MINUTE: 0 to 59
• SECOND: 0 to 59.999999999

[ ] 为可选项
|  表示任选其中一项
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：

 select INTERVAL '5 5:20:12.15' DAY TO SECOND(3) from dual;   +05 05:20:12.150 --表示5天5小时20分12.150秒
 select INTERVAL '5 5:20:12.15' DAY TO SECOND(2) from dual;   +05 05:20:12.15 --表示5天5小时20分12.15秒

案例2：
表示5天5小时20分

select INTERVAL '5 5:20' DAY TO MINUTE from dual;   +05 05:20:00 

案例3：
表示365天5小时, “DAY(3)” 表示天的精度为3, 如果DAY(n), n<3就会报错，ORA-01873间隔的前导精度太小（注意默认是2）

 select INTERVAL '365 5' DAY(3) TO HOUR from dual;   +365 05:00:00 

案例4：
表示365天

select INTERVAL '365' DAY(3) from dual;   +365 00:00:00

案例5：
表示5小时20分12.15555秒

select INTERVAL '5:20:12.15555' HOUR TO SECOND(5) from dual;   +00 05:20:12.15555

案例6：
表示5小时20分

select INTERVAL '5:20' HOUR TO MINUTE from dual;   +00 05:20:00

案例7：

select  INTERVAL '30.12345' SECOND(4) from dual;   +0000 00:00:30.123450 --表示30.123450秒
select  INTERVAL '30.12345' SECOND(2,4) from dual;   +00 00:00:30.1235 --表示30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入

案例8：
表示1天，一天24小时，自动换算成1天

select INTERVAL '24' HOUR  from dual;   +01 00:00:00

案例9：

select INTERVAL '20' DAY - INTERVAL '21' HOUR from dual;   +000000019 03:00:00.000000000 --表示 20天-21小时=19天3小时
select sysdate - (INTERVAL '1' HOUR) from dual;   2021/3/9 12:14:44 --表示当前时间前一个小时时间