oracle 时间函数

select sysdate from dual;


select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM DUAL; 


select systimestamp from dual;


select add_months(sysdate,-2) from dual; --add_months (date,integer) 
                                         --            date: 日期  ，integer 要加的月数 ，如果是负数，表示减去的月份数 
            
 select last_day(to_date('2017-2-1','yyyy-mm-dd')) from dual;  --last_day 返回指定月份的最后一天
 
 select next_day(sysdate,'星期二') from dual;   --next_day(date,char)返回当前日期后一周对应的char的日期 ，char 表示是日期
 
 select EXTRACT(YEAR FROM DATE '1998-03-07') from dual;  --EXTRACT(datetime) 日期中提取年，月，日，分等
 
select extract(month from  sysdate) from dual;


select extract(day from date '2017-05-12') as day,extract(hour from timestamp '2017-05-12 15:14:16') as hours, 
       extract(minute from timestamp '2017-05-12 15:14:16') as min ,
       extract(second from timestamp '2017-05-12 15:14:16') as sec  from dual;


--获取两个日期之间的具体时间间隔，extract函数是最好的选择   
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-05-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)   
/  


--months_between months_between(date1,date2) 返回date1到date2的月份数
select months_between(to_date('2010-12-01','YYYY-MM-DD'),to_date('2010-09-01','YYYY-MM-DD')) from dual;