Oracle 库函数总结（一）

Oracle 库函数总结（一）

Trunc 函数

Trunc 函数是一个取整函数，可以用于日期和数字的取整

日期取整

格式： trunc(d1，[c1])
- d1参数:日期型
- c1参数：[可选]，格式化字符串， 默认为 j （返回当天）

说明：
- 截取日期型d1在c1范围内的第一天

C1 参数：

说明	参数
获取本世纪的第一天	CC 或 SCC
获取当年第一天	YYYY、YYY、YY、Y 或 SYYYY 或 YEAR、SYEAR
获取当年第一周的第一天的日期（ISO 8601 标准）	IYYY、IYY、IY
获取本季度（3个月）的第一天	Q
获取本月第一天	MONTH、MON、MON、RM
以当年第一天重新计算周，获取日期所属的周的第一天	WW
以当月第一天重新计算周，获取日期所属的周的第一天	W
根据ISO 8601 标准，获取日期所属的周的第一天	IW
截取日期到天	DD 、DDD、J
获取日期所属周的第一天	Day、D、DY
截取到小时	HH、HH24、HH12
截取到分钟	MI

截取周的方式的区别

参数	说明	计数方式（周X - 周 X）	计算方式
WW	获取周的第一天	7 - 6	以当年第一天重新计算周
W	获取周的第一天	7 - 6	以当月第一天重新计算周
IW	获取周的第一天	1 - 7	连续
Day、Dy、 D	获取周的第一天	7 - 6	连续
IYYYY 系列	获取当年第一周的第一天	1 - 7	连续

注意

1、week的计算方式的区别
- ISO 8601 week的计算是从周一开始，计算到周天
- oracle 中如果没有声明返回ISO 8601 标准， 返回的week按照周天到周六计算

2、如果没有指定周的计算方式，则默认按照连续的计算方式
3、IYYY 系列没有 IYYYY的参数
4、详细信息参见oracle官方文档 ROUND and TRUNC Date Functions

代码

--获取星期第一天的比较
SELECT TRUNC (TO_DATE ('2016-2-2', 'yyyy-mm-dd'), 'WW'),        --以2016年的第一天重新计算week，2月2日所在的周的第一天为1月29
       TRUNC (TO_DATE ('2016-2-2', 'yyyy-mm-dd'), 'IW'),        --返回所在ISO标准周的第一天，注意从周一到周天计算周
       TRUNC (TO_DATE ('2016-2-2', 'yyyy-mm-dd'), 'W'),         --以当月第一天重新计算week，所以2月2日所在的周的第一天为2月1日
       TRUNC (TO_DATE ('2016-2-2', 'yyyy-mm-dd'), 'day')        --以连续的方式计算周，从周天到周六计算周，所以比IW 计算结果早一天
  FROM DUAL;
--执行结果
--    WW           IW          W           day
-- 2016/1/29    2016/2/1    2016/2/1    2016/01/31

数字取整

格式：TRUNC（number,num_digits）
- 参数number：要截取的数字
- 参数num_digits：要截取的位数，可取正数和负数，正数往小数点向右截取，负数往小数点向左截取，
默认为0

说明：
- 按照指定的位数截取数字

代码

  SELECT  TRUNC (123.456, 3), TRUNC (123.456),TRUNC (123.456, -2) FROM DUAL;
  --执行结果
  --123.456   123    100

add_months 函数

格式：add_months(time,months)
- 参数说明time：时间型
- 参数months：需要计算的月份数

函数说明：
- 得到某一时间之前或之后n个月的时间

代码

select add_months(sysdate,-2) from dual;
--执行结果
--2016/05/21 下午 04:15:03

select add_months(sysdate,2) from dual;
--执行结果
--2016/9/21 下午 04:16:36

INTERVAL系列函数

INTERVAL 系列函数包含两类函数，都用来表示一段时间差,。
- 第一类 ：由格式化时间字符串解析出一段时间差，包含两个函数
- INTERVAL YEAR TO MONTH ：返回年月表示的时间差
- INTERVAL DAY TO SECOND：返回由天、小时、分钟、秒、毫秒表示的时间差

• 第二类：由一个整数和某个时间单位解析一段时间差
  
  • NumtoDsInterval(n, ‘interval_unit’) ：适用于时间单位 DAY, HOUR, MINUTE, SECOND
  • NUMTOYMINTERVAL(n, ‘interval_unit’) ：适用于时间单位YEAR，MONTH

INTERVAL year to month

格式：interval `integer [ - integer]` {Year|month} [(percision)][to {Year|month}]
- integer [ - integer] : 表示”年-月”。例如：123-4 表示123年零4个月。
- {year | month}：表示前面的数字是year或month，必须二选一。
- [(persicion)] ：指定year 或 month 的精度，范围为0-9，默认为2。
- [to {Year|month}]： 表示转换为年或月

函数说明
- 描述一个时间段，只能精确到年月

注意：
- 注意一：最后一个参数[to {Year|month}] ，一般取值位 to month。

select interval '5000-2' year(5) to year from dual;  --错误，因为损失精度，因为含有月份的信息
select interval '5000-2' year(5) to month from dual;  --ok

• 注意二：{Year | month} 具有优先级关系，如果前一个参数有是” 年- 月”格式，则{Year | month}只能是year，如果前一个参数有是” 年（月）”格式，则{Year | month} 既可以是year 也 可以是month。
  

   select INTERVAL '4-2' month TO MONTH from dual;  --错误，应该指定year的精度
   select INTERVAL '4-2' Year TO MONTH from dual;   --ok 

代码

select INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH  from dual; 
select INTERVAL '6-11' YEAR TO MONTH from dual;

INTERVAL day to second

格式：

INTERVAL {integer | integer time_expr | time_expr}
{{ day | hour | minute [(percision)] } | second (leading_percision[,fracational_percision])}
[to {day | hour | minute | seconds [(fracational_percision)] }]

• 参数time_expr：表示时间格式，格式如 day [HH[:MI[:SS[.n]]]]或 HH[:MI[:SS[.n]]] 或 MI[:SS[.n]] 或 SS[.n]

  • DAY 天， HH 小时，MI分钟，SS 秒，n 毫秒

• 参数fracational_percision：表示秒的小数位数，即毫秒的精度

• 其余参数和 INTERVAL year to month 相同

函数说明：

整个函数由三部分组成
- 第一部分：上述格式的第一行，指定表示一段时间字符串
- 第二部分：上述格式的第二行，指定time_expr的最大时间单位
- 第三部分：上述格式的第三行，指定time_expr的最小时间单位

代码

SELECT INTERVAL '10 23:50:30.600' DAY TO SECOND (3) FROM DUAL;      -- +10 23:50:30.600000
SELECT INTERVAL '23:50:30.600' HOUR TO SECOND (3) FROM DUAL;        -- +00 23:50:30.600000
SELECT INTERVAL '23:50' HOUR TO MINUTE FROM DUAL;                   -- +00 23:50:00.000000
SELECT INTERVAL '1 23:50' DAY TO MINUTE FROM DUAL;                  -- +01 23:50:00.000000
SELECT INTERVAL '1 23' DAY TO HOUR FROM DUAL;                       -- +01 23:00:00.000000  
SELECT INTERVAL '12:23.569' MINUTE TO SECOND (2) FROM DUAL;         -- +00 00:12:23.570000
SELECT INTERVAL '1.569' SECOND (1,2) FROM DUAL;                     -- +00 00:00:01.570000

NUMTOYMINTERVAL函数

格式：NUMTOYMINTERVAL（n，’interval_unit’）
- 参数n ：要转换的时间的大小
- 参数interval_unit ： 要转换的时间的单位，只能取 YEAR，MONTH

说明：
- 由一个整数和某个时间单位返回一段时间差

代码

select NUMTOYMINTERVAL(100,'MONTH') from dual;                      -- +08-04
select NUMTOYMINTERVAL(100,'Year') from dual;                       -- +100-00    

NUMTODSINTERVAL函数

格式：NUMTODSINTERVAL（n，’interval_unit’）
- 参数n ：要转换的时间的大小
- 参数interval_unit ： 要转换的时间的单位，只能取DAY, HOUR, MINUTE, SECOND

说明：
- 由一个整数和某个时间单位返回一段时间差

代码

select NUMTODSINTERVAL(100,'DAY') from dual;                       -- +100 00:00:00.000000
select NUMTODSINTERVAL(100,'HOUR') from dual;                      -- +04 04:00:00.000000
select NUMTODSINTERVAL(100,'MINUTE') from dual;                    -- +00 01:40:00.000000
select NUMTODSINTERVAL(100,'SECOND') from dual;                    -- +00 00:01:40.000000