oracle中extract()函数----用于截取年、月、日、时、分、秒 有大用

extract (    

        { year | month | day | hour | minute | second }    

        | { timezone_hour | timezone_minute }    

        | { timezone_region | timezone_abbr }    

from { date_value | interval_value } )

SQL> select  extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from  dual;

      YEAR      MONTH        DAY
---------- ---------- ----------
      2015          5          4

SQL> select extract (year from date '2015-05-04') year, extract (month from date'2015-05-04') month, extract (day from date '2011-05-04') day from dual;

      YEAR      MONTH        DAY
---------- ---------- ----------
      2015          5          4

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

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
)

select extract(year from interval '21' year) year from dual

interval '{ integer | integer time_expr | time_expr }' 

{ { day | hour | minute } [ ( leading_precision ) ] 

| second [ ( leading_precision [, fractional_seconds_precision ] ) ] } 

[ to { day | hour | minute | second [ (fractional_seconds_precision) ] } ]