Oracle中extract()函数的用法

来源：互联网发布：上海贝格数据怎么样编辑：程序博客网时间：2024/06/08 00:11

（1）先看Oracle 11g官方文档：

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm

（2）Oracle 从9i引入了extract()函数，用于从date类型或interval类型中截取到特定的部分；

（3）语法：extract (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

| { TIMEZONE_HOUR | TIMEZONE_MINUTE }

| { TIMEZONE_REGION | TIMEZONE_ABBR }

FROM{ date_value | interval_value } )

（4）从date类型(YYYY-MM-DD)中只能截取year,month,day;

select extract(year from sysdate) from dual;select extract(month from sysdate) from dual;select extract(day from sysdate) from dual;select extract(year from date'2013-11-30') year from dual;select extract(month from date'2013-11-30') month from dual;select extract(day from date'2013-11-30') day from dual;

select extract(hour from timestamp '2013-11-30 20:18:20') from dual;select extract(minute from timestamp '2013-11-30 20:18:20') from dual;select extract(timezone_region from timestamp '2013-11-30 22:00:00 -8:00') from dual;

select extract(year from systimestamp) year from dual;select extract(month from systimestamp) month from dual;select extract(day from systimestamp) day from dual;select extract(minute from systimestamp)minute from dual;select extract(second from systimestamp)second from dual;select extract(timezone_hour from systimestamp) th from dual;select extract(timezone_minute from systimestamp) tm from dual;select extract(timezone_region from systimestamp) tr from dual;select extract(timezone_abbr from systimestamp) ta from dual;

（5）获取两个日期之间的具体时间间隔，可以用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) secondfrom( select to_timestamp('2013-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,to_timestamp('2013-11-30 22:08:46', 'yyyy-mm-dd hh24:mi:ss') dt2 from dual);