/oracle中extract()函數(shù)從oracle 9i中引入,用于從一個date或者interval類型中截取到特定的部分 //語法如下： EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM
/Oracle中extract()函數(shù)從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 的數(shù)據(jù)類型中截取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函數(shù)是最好的選擇??
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??
//?
