(1)先看Oracle 11g官方文档:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm
(2)Oracle 从9i引入了extract()函数,用于从date类型或interval类型中截取到特定的部分;
(3)语法:extract (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM{ date_value | interval_value } )
(4)从date类型(YYYY-MM-DD)中只能截取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 extract(year from date'2013-11-30') year from dual;
select extract(month from date'2013-11-30') month from dual;
select extract(day from date'2013-11-30') day from dual;
select extract(hour from timestamp '2013-11-30 20:18:20') from dual;
select extract(minute from timestamp '2013-11-30 20:18:20') from dual;
select extract(timezone_region from timestamp '2013-11-30 22:00:00 -8:00') from dual;
select extract(year from systimestamp) year from dual;
select extract(month from systimestamp) month from dual;
select extract(day from systimestamp) day from dual;
select extract(minute from systimestamp)minute from dual;
select extract(second from systimestamp)second from dual;
select extract(timezone_hour from systimestamp) th from dual;
select extract(timezone_minute from systimestamp) tm from dual;
select extract(timezone_region from systimestamp) tr from dual;
select extract(timezone_abbr from systimestamp) ta from dual;
(5)获取两个日期之间的具体时间间隔,可以用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('2013-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,to_timestamp('2013-11-30 22:08:46', 'yyyy-mm-dd hh24:mi:ss') dt2 from dual
);