Oracle日期处理——Extract()函数

51wy6r9qco

1.2个日期相差的天数：
--相减得到相差的天数
select to_date('2012-10-18 15:29:01', 'yyyy-mm-dd hh24:mi:ss') -
to_date('2012-08-09:18:04:02', 'yyyy-mm-dd hh24:mi:ss')
from dual;
2.相隔的天数，小时，分钟，秒数：
select extract(day from ts2 - ts1) day,
extract(hour from ts2 - ts1) hour,
extract(minute from ts2 - ts1) minute,
extract(second from ts2 - ts1) second
from (select to_timestamp('2011-10-18 15:29:02', 'yyyy-mm-dd hh24:mi:ss') ts1,
to_timestamp('2012-12-26 17:22:05', 'yyyy-mm-dd hh24:mi:ss') ts2
from dual);
--从date中截取year,month,day
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) 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;
--从timestamp截取year,month,day,hour,minute,second
select extract(year from timestamp'2012-12-10 12:02:01') from dual;
select extract(month from timestamp'2012-12-10 12:02:01') from dual;
select extract(day from timestamp'2012-12-10 12:02:01') from dual;
select extract(hour from timestamp'2012-12-10 12:02:01') from dual;
select extract(minute from timestamp'2012-12-10 12:02:01') from dual;
select extract(second from timestamp'2012-12-10 12:02:01') from dual;

--在当前时间减去3年
select   sysdate,sysdate - interval '3' year  from  dual;
--在当前时间减去3月
select   sysdate,sysdate - interval '3' month  from  dual;
--在当前时间减去3天
select   sysdate,sysdate - interval '3' day  from  dual;
--在当前时间减去3小时
select   sysdate,sysdate - interval '3' hour  from  dual;
--在当前时间减去3分钟
select   sysdate,sysdate - interval '3' MINUTE  from  dual;
--在当前时间减去3秒
select   sysdate,sysdate - interval '3' second  from  dual;
--在当前时间减去15秒
select   sysdate,sysdate - 5*interval '3' second  from  dual;
--在当前时间+15秒
select   sysdate,sysdate + 5*interval '3' second  from  dual;
--获取某个月的天数
select to_char(last_day(sysdate),'dd') from dual;
--获取季度
select  to_char(sysdate,'Q')  from dual;