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(hour from systimestamp) hour
,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 hour MINUTE SECOND TH TM TR TA

---------- ---------- ---------- ------- ---------- ---------- ------ ------ --------- ----------

2018      9         28       14             13     38.757618     0      0     UNKNOWN   UNK

//获取年月日

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 sysdate)+1 from dual; //获取下一年度

巴特西

oracle extract 函数简介

最新文章

热门文章