oracle 日期 extract,Oracle日期处理——Extract()函数

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;