//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(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 MINUTE
SECOND
TH TM
TR TA ----------
---------- ---------- ---------- ---------- ---------- ---------- ---------
---------- 2011
5
17
7
14.843
8 0 UNKNOWN
UNK //
http://www.cnblogs.com/zq281660880/archive/2012/11/09/2762179.html
oracle Extract 函数,布布扣,bubuko.com
原文：http://www.cnblogs.com/seasonzone/p/3580549.html