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  
    //  

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression.
The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, 
MONTH, and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from 
the TIMESTAMP WITH TIME ZONE datatype.
eg.

    SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current Year
    SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current Month
    SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Day
    SELECT EXTRACT(HOUR FROM SYSDATE) FROM DUAL;Error

       Correct:SELECT EXTRACT(HOUR FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;

    SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;Error

       Correct:SELECT EXTRACT(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;

       以上是EXTRACT()函数的使用说明,英文部分有个很重要的部分.就是extract 抽取年月日部分和时分部分的使用方法是不同的.
EXTRACT(YEAR FROM SYSDATE)中FROM 的下一个参数是日期类型的时候可以抽取年月日.就是说这个参数是日期类型的时候就可以抽取年\月\日,而抽取不了时\分部分.
抽取时分部分得在FROM 后面加带TIMESTAMP 且后面加时间字符.这里要注意,在(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10')中,如果把'2005-10-10 10:10:10'用
日期类型的字段来代替并转换成字符类型后,也是错误的,估计是这方法的问题.
    比如(MINUTE FROM TIMESTAMP to_date(to_char(日期类型,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss')),这样执行不了的.


    如果真要从日期类型的字段中抽取时\分部分用to_char() 函数就可以了.比如to_char(日期类型,'hh24:mi')