oracle Extract 函式
//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   
//   
oracle Extract 函式

相關文章
