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类型中截取年月日

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

只可以从一个date类型中截取年月日

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

从timestamp中获取年月日时分秒

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

获取两个日期之间的具体时间间隔

获取两个日期之间的具体时间间隔，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

)

获取interval类型特定部分

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

Interval 语法

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) ] } ]

leading_precision值的范围是0到9, 默认是2. time_expr的格式为:hh[:mi[:ss[.n]]] or mi[:ss[.n]] or ss[.n], n表示微秒.

范围值:

hour: 0 to 23

minute: 0 to 59

second: 0 to 59.999999999

eg: