Oracle EXTRACT()函数

//oracle中extract()函数从oracle9i中引入,用于从一个date或者interval类型中截取到特定的部分

1. //语法如下：
2. EXTRACT(
3. {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
4. |{ TIMEZONE_HOUR | TIMEZONE_MINUTE }
5. |{ TIMEZONE_REGION | TIMEZONE_ABBR }
6. FROM{ date_value | interval_value } )
7. //我们只可以从一个date类型中截取year,month,day(date日期的格式为yyyy-mm-dd);
8. //我们只可以从一个timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
9. selectextract(year from date'2011-05-17')year from dual;
10. YEAR
11. ----------
12. 2011
13. selectextract(month from date'2011-05-17')month from dual;
14. MONTH
15. ----------
16. 5
17. selectextract(day from date'2011-05-17')day from dual;
18. DAY
19. ----------
20. 17
21. //获取两个日期之间的具体时间间隔，extract函数是最好的选择
22. selectextract(day from dt2-dt1) day
23. ,extract(hourfrom dt2-dt1) hour
24. ,extract(minutefrom dt2-dt1) minute
25. ,extract(secondfrom dt2-dt1) second
26. from(
27. selectto_timestamp('2011-02-04 15:07:00','yyyy-mm-ddhh24:mi:ss') dt1
28. ,to_timestamp('2011-05-1719:08:46','yyyy-mm-ddhh24:mi:ss') dt2
29. fromdual)
30. /
31. DAYHOUR MINUTE SECOND
32. -------------------- ---------- ----------
33. 1024 146
34. --
35. selectextract(year from systimestamp) year
36. ,extract(monthfrom systimestamp) month
37. ,extract(dayfrom systimestamp) day
38. ,extract(minutefrom systimestamp) minute
39. ,extract(secondfrom systimestamp) second
40. ,extract(timezone_hourfrom systimestamp) th
41. ,extract(timezone_minutefrom systimestamp) tm
42. ,extract(timezone_regionfrom systimestamp) tr
43. ,extract(timezone_abbrfrom systimestamp) ta
44. fromdual
45. /
46. YEARMONTH DAY MINUTE SECOND THTM TR TA
47. -------------------- ---------- ---------- ---------- ---------- ---------- --------- ----------
48. 20115 177 14.8438 0UNKNOWN UNK