Oracle小知识

--2017/12/28

日期函数interval和add_months

  今天在工作中发现oracle中的interval函数对日期处理时，只会对所给的日期参数做处理，不会自动识别日期;

例如 select date'2017-12-31' - interval '1' month from dual 是无法运行的，因为此函数只会对月份(month)做处理，不会识别日期，而11月是不存在31号的，此时推荐使用函数add_months:

  select add_months(date'2016-12-31',-1)  from dual;

同理，在闰年的二月月底也会发生这种情况。

  当需求改为取非二月的每个月的30号时，此时不能用add_months，add_months会自动识别日期是否为当月最后一天

例如 select add_months(date'2016-11-30',1) from dual 的结果为2017-12-31，此时interval函数将会成为第一选择。

下面附一下常见日期处理的方法：

--求某天是星期几
select to_char(to_date('2002-8-26','yyyy-MM-dd'),'day') from dual;
--英文星期
select to_char(to_date('2002-08-26','yyyy-MM-dd'),'day'
               ,'NLS_DATE_LANGUAGE=American')from dual;
--设置日期语言
alter session set NLS_DATE_LANGUAGE='AMERICAN';
trunc()函数


  TRUNC(NUMBER,NUM_DIGITS)  --NUMBER 需要截尾取整的数字。  num_digits 用于指定取整精度的数字 默认为0
          --trunc函数截取时不进行4舍5入
  
  trunc(sysdate) - 当日 
  trunc（sysdate,'mm'）- 当月第一天 
  trunc（sysdate,'y/yy'）-当年第一天
  trunc(sysdate,'dd') - 一天之始 
  trunc(sysdate,'d/day') - 当前星期第一天（星期天）  
  trunc(sysdate,'yyyy') - 当年第一天
  trunc（sysdate,'hh'） - 当前小时  
  trunc(sysdate,'mi') - 当前分钟
  last_day(sysdate) - 月末
  to_char(sysdate,'day') - 周几
  to_char(sysdate,'month') - 月份
--求两个日期之间的天数
select floor(sysdate-to_date('19930715','yyyyMMdd'))from dual;
--求两个日期之间的月数
select max_hd - min_hd 间隔天,
       months_between(max_hd,min_hd) 间隔月,
       months_between(max_hd,min_hd)/12 间隔年
from (select min(hiredate) min_hd,
             max(hiredate) max_hd
     from emp) x;       
--时间为null的用法   并集（全部）
select t.empno,t.hiredate from emp t
union
select 1,to_date(null) from dual;
--处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) + 1, -2), 'yyyyMMdd'),
       last_day(sysdate)
  from dual;
--找出今年的天数
select add_months(trunc(sysdate, 'year'), 12) - trunc(sysdate, 'year')
  from dual;
--闰年的处理方法
to_char(last_day(to_date('02'||:year,'mmyyyy')),'dd')   如果是28就不是闰年
--五分钟一个间隔
select to_date(floor(to_char(sysdate, 'sssss') / 300) * 300, 'sssss'),
       to_char(sysdate, 'sssss')
  from dual;
--一年的第几天
select to_char(sysdate,'ddd'),sysdate from dual;
--下一个星期6，周日为1周六为7
select next_day(sysdate,7) from dual;
--两个日期差的年月日
floor((date2-date1)/365)  作为年
floor((date2-date1)/30)   作为月  floor 向下
mod(mod(date2-date1,365),30)  作为日  mod 取余
select floor((sysdate-to_date('2002-08-26','yyyy-MM-dd'))/365) from dual;
select mod(mod(sysdate-to_date('2002-08-26','yyyy-MM-dd'),365),30) from dual;
select floor(sysdate-to_date('2002-8-26','yyyy-MM-dd')) from dual;
select mod(mod(5457,365),30) from dual;
select mod(347,30) from dual;
--extract找出日期或间隔值的字段值
select extract(year或month或day或hour或minute或second from date'2011-05-17') year... from dual;
select extract(day from dt2 - dt1) day
      ,extract(hour from dt2 - dt1) hour
      ,extract(minute from dt2 - dt1) minute
      ,extract(second from dt2 - dt1) second
  from (select to_timestamp('2011-02-04 15:07:00', 'yyyy-mm-dd hh24:mi:ss') dt1
       ,to_timestamp('2011-05-17 19:08:46', 'yyyy-mm-dd hh24:mi:ss') dt2
from dual);


select extract(year from systimestamp) year  
      ,extract(month from systimestamp) month  
      ,extract(day from systimestamp) day  
      ,extract(minute from systimestamp) minute  
      ,extract(second from systimestamp) second  
      ,extract(timezone_hour from systimestamp) th   
      ,extract(timezone_minute from systimestamp) tm   
      ,extract(timezone_region from systimestamp) tr   
      ,extract(timezone_abbr from systimestamp) ta   
from dual;


select localtimestamp as 会话中的日期和时间,systimestamp as 本机数据库上当前系统日期和时间,sysdate from dual;
 
select trunc(to_date('2002-08-26','yyyy-MM-dd')) as 当日 from dual;
select trunc(to_date('2002-08-26','yyyy-MM-dd'),'mm') as 月首日 from dual;
select trunc(to_date('2002-08-26','yyyy-MM-dd'),'yyyy') as 年首日 from dual;
select trunc(to_date('2002-08-26','yyyy-MM-dd'),'dd')as 当日 from dual;
select trunc(to_date('2002-08-26 10:02:34','yyyy-MM-dd  hh24:mi:ss'),'hh')精确到小时 from dual;
select trunc(to_date('2002-08-26 10:02:34','yyyy-MM-dd  hh24:mi:ss'),'mi')精确

分钟 from dual;