//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);
//我们只可以从一个 timestampwith
time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
select
extract(year fromdate'2011-05-17')year
fromdual;
YEAR
2011
select
extract(month fromdate'2011-05-17')month
fromdual;
MONTH
5
select
extract(day fromdate'2011-05-17')day
from dual;
DAY
17
//获取两个日期之间的具体时间间隔，extract函数是最好的选择
select
extract(day fromdt2-dt1)
day
,extract(hourfrom
dt2-dt1) hour
,extract(minutefrom
dt2-dt1) minute
,extract(secondfrom
dt2-dt1) second
from
(
selectto_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
fromdual)
/
DAY HOUR
MINUTE SECOND
102 4 1 46
select
extract(year fromsystimestamp)
year
,extract(monthfrom
systimestamp) month
,extract(dayfrom
systimestamp) day
,extract(minutefrom
systimestamp) minute
,extract(secondfrom
systimestamp) second
,extract(timezone_hourfrom
systimestamp) th
,extract(timezone_minutefrom
systimestamp) tm
,extract(timezone_regionfrom
systimestamp) tr
,extract(timezone_abbrfrom
systimestamp) ta
from
dual
/
YEAR MONTH
DAY MINUTE
SECOND TH TM TR TA
2011 5 17 7 14.843 8 0 UNKNOWN UNK
//
