Oracle日期处理整理

1.获取日期元素
注意：
1).hh24写法指24小时，Oracle默认是12小时
2).分钟用mi，不要用mm，因为与之前的MM冲突
    1-12小时写法 yyyyMMdd24miss（Oracle默认）
    1-24小时写法 yyyyMMddHH24miss
获取日期元素：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 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 to_char(to_date('20200805','yyyy-mm-dd hh24:mi:ss'),'w') FROM dual;

ww:按每年的1月1日为第一周第一天，1月8日为第二周第一天
w:与ww功能类似，不过它是按每月的1日为每周第一天，例如1日为当月第一周第一天，8日为第二周第一天
iw:自然周，为星期一至星期日算一周，且每年的第一个星期一为第一周

--查询今年第几周
select to_char(sysdate,'ww') from dual; --每天第一天为第一周第一天
select to_char(sysdate,'iw') from dual; --自然周
-- 查询本月第几周
select to_char(sysdate,'w') from dual;

select trunc(sysdate,'d')+1 from dual; --周日为一周起始日期
select trunc(sysdate,'iw') from dual;  --周日为一周结束日期

时间精确到毫秒用timestamp
-- 时间精确到毫秒
select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff ') from dual;
select to_timestamp('2003-10-2410:48:45.656000','yyyy-mm-dd hh24:mi:ssxff') from dual;
select to_char(systimestamp,'yyyy-mm-ddhh24:mi:ssxff') time1, to_char(current_timestamp) time2 from dual;
select to_char(systimestamp, 'yyyymmddhh24missff'),to_char(systimestamp, 'yyyymmddhh24miss'),to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff ') from dual;
select substr(to_char(systimestamp, 'yyyymmddhh24missff'),0,17) from dual;
select cast(systimestamp as date) from dual;

2.日期转换
select to_char(SYSDATE,'yyyyMMddHH24miss') from dual;
select to_date('20200803140816','yyyyMMddHH24miss') from dual;
select to_date('2020-08-03 00:00:00','yyyy-mm-dd hh24:mi:ss') FROM dual;

3.trunc：截断到最接近的日期,单位为天
select trunc(sysdate, 'mm') from dual -- 返回当月第一天.
select trunc(sysdate,'yy') from dual -- 返回当年第一天
select trunc(sysdate,'dd') from dual --返回当前年月日
select trunc(sysdate,'yyyy') from dual --返回当年第一天
select trunc(sysdate,'d') from dual --返回当前星期的第一天:星期天
select trunc(sysdate, 'hh') from dual --返回当前小时时间
select trunc(sysdate, 'mi') from dual --TRUNC()函数没有秒的精确

select trunc(to_date(last_day(sysdate))) + （8*60+30）/ (24*60) from dual;--获取当前月最后一天2020/8/31 8:30:00
select trunc(sysdate+1) + (8 * 60+30) / (24 * 60) from dual;--获取下一天的8点半日期2020/8/4 8:30:00

4.日期加减
加：
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; --加1月
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒

减：
select sysdate,add_months(sysdate,-12) from dual; --减1年
select sysdate,add_months(sysdate,-1) from dual; --减1月
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期
select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天
select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时
select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟
select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒

5.round:返回指定的小数位元数进行四舍五入运算的结果
select round(123.456, 0) from dual; -- 123
select round(123.456, 1) from dual; -- 123.5
select round(123.456, 2) from dual; -- 123.46
select round(123.456, 3) from dual; -- 123.456
select round(-123.456, 2) from dual; -- -123.46

6.计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日
select floor(to_number(sysdate-to_date('2020-08-03 18:25:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual --时间差-年
select ceil(months_between(sysdate,to_date('2020-08-03 18:25:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual --时间差-月
select floor(to_number(sysdate-to_date('2020-08-03 18:25:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual --时间差-天
select floor(to_number(sysdate-to_date('2020-08-03 18:25:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual --时间差-时
select floor(to_number(sysdate-to_date('2020-08-03 18:25:03','yyyy-mm-dd hh24:mi:ss')) * 24 * 60) as spanMinutes from dual --时间差-分
select floor(to_number(sysdate-to_date('2020-08-03 18:25:03','yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60) as spanSeconds from dual --时间差-秒

7.定时器用到的时间
1) 每分钟执行
Interval => TRUNC(sysdate,'mi') + 1 / (24*60)

2) 每天定时执行
例如：每天的凌晨2点执行
Interval => TRUNC(sysdate) + 1 +2 / (24)

3) 每周定时执行
例如：每周一凌晨2点执行
Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

4) 每月定时执行
例如：每月1日凌晨2点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

5) 每季度定时执行
例如每季度的第一天凌晨2点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

6) 每半年定时执行
例如：每年7月1日和1月1日凌晨2点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

7) 每年定时执行
例如：每年1月1日凌晨2点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24