/oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分 //语法如下： extract ( { year | month | day | hour | minute | second } | { timezone_hour | timezone_minute } | { timezone_region | timezone_abbr } from
/oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分 
//语法如下：  
extract (  
        { year | month | day | hour | minute | second }
| { timezone_hour | timezone_minute }  
        | { timezone_region | timezone_abbr }  
from { date_value | interval_value } )  
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);  
//我们只可以从一个 timestamp with time zone 的数据类型中截取timezone_hour和timezone_minute;  
select extract(year from date'2011-05-17') year from dual;  
      year  
----------  
      2011  
select extract(month from date'2011-05-17') month from dual;  
     month  
----------  
         5  
select extract(day from date'2011-05-17') day from dual;  
       day  
----------  
        17  
//获取两个日期之间的具体时间间隔，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-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)  
/  
       day       hour     minute     second  
---------- ---------- ---------- ----------  
       102          4          1         46  
--  
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  
/  
     year      month        day     minute     second         th         tm tr         ta  
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------  
      2011          5         17          7     14.843          8          0 unknown   unk  
//