Oracle时间处理函数大全（附超全面案例）

Oracle常用时间函数

add_months(date，int)：输入date数据和需要增加的月份数（整数），返回增加若干月份数后date数据。

 -- 加一个月后的日期
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual;
-- 扩展
-- 求当年的天数：增加12月后再取年份相减
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') days FROM dual;

last_day(date):返回当前日期在该月份的最后一天的日期

SELECT LAST_DAY(SYSDATE) from dual;
-- 假设今天为2021-04-21，那么执行返回的时间日期为2021-04-30

months_between(date1，date2)：返回两个日期的月份差，存在小数。

-- 返回值刚好为1，因为都是两个月份的02相减。
-- 提示：如果不相等则有很多小数。这时候使用floor进行向下取整或round进行向上取整
SELECT MONTHS_BETWEEN(to_date('2021-05-02','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd')) as month from dual;

项目实际案例

加减时间（年月日时分秒）

-- 当前时间减去10分钟，同理换成year（年）、month（月）、day（日）、hour（时）、second（秒）
select sysdate,sysdate - interval '10' minute from dual;
-- 也可以换成加10分钟。
select sysdate,sysdate + interval '10' minute from dual;

求时间为星期几或者几月份或多少年

select to_char(sysdate,'D') from dual; --这周的第几天；每周的第一天为星期天，星期一为第二天
--某天星期几 同样可以求多少年(year)多少月(month)
select to_char(to_date('2021-04-08','yyyy-mm-dd'),'day') from dual; --20210408为星期四
--输出英文。某天星期几 同样可以求多少年多少月
select to_char(to_date('2021-04-08','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = AMERICAN') from dual; 

求两个日期之间的月份

-- 求两个日期之间的月份（如果日期存在差值，则会有很长的小数,使用floor函数可以向下取整。）
-- 结果0，不满一个月
SELECT floor(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd'))) as month from dual;  
-- 求两个日期之间的月份（如果日期存在差值，则会有很长的小数,使用round函数可以四舍五入。）
-- 结果1，过了一半就算一个月。根据需求选择
SELECT ROUND(MONTHS_BETWEEN(to_date('2021-05-01','yyyy-mm-dd'), to_date('2021-04-02','yyyy-mm-dd')),0) as month from dual;

-- 扩展
-- 求人员的年龄
-- 月份相减计算年龄（推荐）
SELECT TRUNC(MONTHS_BETWEEN(to_date('2021-04-01','yyyy-mm-dd'), to_date('1997-04-02','yyyy-mm-dd'))/12,0) as age from dual;  

有关Oracle数字型数据的处理相关函数，请查看这篇文章：Oracle处理数字型数据。比如说floor、trunc都有介绍。

求两个日期的天数

--两个日期间的天数差，同理除365获得年，除30得到月。乘于24得到小时差
select floor(TO_NUMBER(sysdate - to_date('20200405','yyyymmdd'))) from dual;

计算年龄

 -- 月份相减计算年龄（推荐）
SELECT TRUNC(MONTHS_BETWEEN(to_date('2021-04-01','yyyy-mm-dd'), to_date('1997-04-02','yyyy-mm-dd'))/12,0) as age from dual; 
--年份相减计算年龄
SELECT TO_CHAR(to_date('2021-04-01','yyyy-mm-dd'),'YYYY') - TO_CHAR(to_date('1997-04-02','yyyy-mm-dd'),'YYYY') as age from dual; 

计算分钟差

-- 求两个时间的分钟差 什么都不乘代表天数，不能四舍五入
SELECT floor(TO_NUMBER(SYSDATE - TO_DATE('2021-04-15 14:05:20', 'YYYY-MM-DD hh24:mi:ss')) * 24 *60) as time from dual;

计算一个月中，星期一至星期五的天数

 -- 求一个月中除开星期六和星期天的天数
 -- 思路：获取累加数，通过日期累加rownum在tochar取星期几判断即可
 -- 注意：星期日为一周的开始即是1，星期一为2以此类推
select count(*)     
   from (select rownum-1 rnum from all_objects where rownum <= to_date('2002-04-30','yyyy-mm-dd') - to_date('2002-04-01','yyyy-mm-dd')+1)
   where to_char(to_date('2021-04-01','yyyy-mm-dd')+rnum,'D') not in ('1','7');

求今天是这个星期的第几天，这个月第几天，今年第几天

select to_char(sysdate,'D') from dual; --这个星期的第几天

select to_char(sysdate,'DD') from dual; --这个月的第几天

select to_char(sysdate,'DDD') from dual; --今年的第几天

求不同时间格式的第一天

-- 通过trunc函数截取标准不同，获取不同时间的开始
select TRUNC(sysdate,'DD') from dual;-- 今天的开始

select TRUNC(sysdate,'iw') from dual;-- 这周的第一天

select TRUNC(sysdate,'MM') from dual;-- 这个月的第一天

select TRUNC(sysdate,'q') from dual;-- 这个季度的第一天

select TRUNC(sysdate,'YYYY') from dual; --今年第一天

时间格式化

-- 时间格式化为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; 
-- 返回的是自然周于本年的周数，一年周数最少不低于52  
select to_char(SYSDATE,'iw') from dual;
--返回季度
select to_char(sysdate,'q') from dual; 
--当前时间的年 
select to_char(sysdate,'yyyy') from dual; 
--当前时间的月   
select to_char(sysdate,'mm') from dual;  
--当前时间的日   
select to_char(sysdate,'dd') from dual;  
--当前时间的时  
select to_char(sysdate,'hh24') from dual;
--当前时间的分     
select to_char(sysdate,'mi') from dual; 
--当前时间的秒    
select to_char(sysdate,'ss') from dual;   

求两个时间的天数、小时、分钟和秒数差

-- 求两个时间的天数、小时差、分钟差和秒数差
SELECT 
	time,
	floor(time) day,
	case when time < 1 then TRUNC(time*24) when time > 1 then MOD(TRUNC(time*24), 24) else 0 END as hour,
	TRUNC(time*24*60 - 60*TRUNC(time*24)) minute, -- 取整再乘60分再相减就可以得出小数部分的分钟数
	TRUNC(time*24*60*60 - 60*TRUNC(time*24*60)) second
FROM 
	(SELECT TO_NUMBER(SYSDATE - TO_DATE('2021-04-15 14:05:20', 'YYYY-MM-DD hh24:mi:ss')) as time from dual) 

求当月天数

-- extract(day from date) day为时间关键字，date为日期
-- 使用extract函数获取当月第一天到date的天数
SELECT extract(day from last_day(sysdate)) FROM dual;