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 months Kochhar 15 years 8 months De Haan 12 years 4 months Hunold 15 years 4 months Ernst 14 years 0 months Austin 7 years 11 months Pataballa 7 years 3 months Lorentz 6 years 3 months Greenberg 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
- SELECT EXTRACT(
DAY
- SELECT EXTRACT(
HOUR
FROM SYSDATE) FROM DUAL;Error
Correct:SELECT EXTRACT(HOUR
FROMTIMESTAMP '2005-10-10 10:10:10') FROM DUAL;
- SELECT EXTRACT(
MINUTE
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')