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; //获取下一年度