extract—oracle时间抽取函数用法

来源：互联网发布：学生会网络部工作总结编辑：程序博客网时间：2024/06/08 05:47

extract—oracle时间抽取函数用法：

SELECT last_name, EXTRACT(YEAR FROM (SYSDATE - hire_date) YEAR TO MONTH )   || ' years '   || EXTRACT(MONTH FROM (SYSDATE - hire_date) YEAR TO MONTH )   || ' months'  "Interval"FROM employees ;LAST_NAME                 Interval------------------------- --------------------King                      17 years 11 monthsKochhar                   15 years 8 monthsDe Haan                   12 years 4 monthsHunold                    15 years 4 monthsErnst                     14 years 0 monthsAustin                    7 years 11 monthsPataballa                 7 years 3 monthsLorentz                   6 years 3 monthsGreenberg                 10 years 9 months. . .SELECT order_id,   EXTRACT(DAY FROM (SYSDATE - order_date) DAY TO SECOND )   || ' days '   || EXTRACT(HOUR FROM (SYSDATE - order_date) DAY TO SECOND )   || ' hours' "Interval"FROM orders;  ORDER_ID Interval---------- --------------------      2458 2095 days 18 hours      2397 2000 days 17 hours      2454 2048 days 16 hours      2354 1762 days 16 hours      2358 1950 days 15 hours      2381 1823 days 13 hours      2440 2080 days 12 hours      2357 2680 days 11 hours      2394 1917 days 10 hours      2435 2078 days 10 hours

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current Year
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current Month
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Day
SELECT EXTRACT(HOUR FROM SYSDATE) FROM DUAL;Error

       Correct:SELECT EXTRACT(HOUR FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;
SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;Error 
       Correct:SELECT EXTRACT(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL; 

       以上是EXTRACT()函数的使用说明,英文部分有个很重要的部分.就是extract 抽取年月日部分和时分部分的使用方法是不同的.
EXTRACT(YEAR FROM SYSDATE)中FROM 的下一个参数是日期类型的时候可以抽取年月日.就是说这个参数是日期类型的时候就可以抽取年/月/日,而抽取不了时/分部分.抽取时分部分得在FROM 后面加带TIMESTAMP 且后面加时间字符.
   这里要注意,在(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10')中,如果把'2005-10-10 10:10:10'用日期类型的字段来代替并转换成字符类型后,也是错误的,估计是这方法的问题.
    比如(MINUTE FROM TIMESTAMP to_date(to_char(日期类型,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss')),这样执行不了的.


    如果真要从日期类型的字段中抽取时/分部分用to_char() 函数就可以了.比如to_char(日期类型,'hh24:mi')