EXTRACT (datetime)
- 语法
EXTRACT( { { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } } FROM { expr } )
expr可以是任何与请求字段兼容的datetime或interval数据类型的表达式。
英文不好的朋友,可以直接去看 下面的案例,通俗易懂。
If
YEAR
orMONTH
is requested, thenexpr
must evaluate to an expression of datatypeDATE
,TIMESTAMP
,TIMESTAMP
WITH
TIME
ZONE
,TIMESTAMP
WITH
LOCAL
TIME
ZONE
, orINTERVAL
YEAR
TO
MONTH
.If
DAY
is requested, thenexpr
must evaluate to an expression of datatypeDATE
,TIMESTAMP
,TIMESTAMP
WITH
TIME
ZONE
,TIMESTAMP
WITH
LOCAL
TIME
ZONE
, orINTERVAL
DAY
TO
SECOND
.If
HOUR
,MINUTE
, orSECOND
is requested, thenexpr
must evaluate to an expression of datatypeTIMESTAMP
,TIMESTAMP
WITH
TIME
ZONE
,TIMESTAMP
WITH
LOCAL
TIME
ZONE
, orINTERVAL
DAY
TO
SECOND
.DATE
is not valid here, because Oracle Database treats it as ANSIDATE
datatype, which has no time fields.If
TIMEZONE_HOUR
,TIMEZONE_MINUTE
,TIMEZONE_ABBR
,TIMEZONE_REGION
, orTIMEZONE_OFFSET
is requested, thenexpr
must evaluate to an expression of datatypeTIMESTAMP
WITH
TIME
ZONE
orTIMESTAMP
WITH
LOCAL
TIME
ZONE
.
案例:
- 01下面的示例从oe.orders表返回,订单表每月订单数量:
SELECT EXTRACT(month FROM order_date) "Month", COUNT(order_date) "No. of Orders" FROM orders GROUP BY EXTRACT(month FROM order_date) ORDER BY "No. of Orders" DESC; Month No. of Orders ---------- ------------- 11 15 7 14 6 14 3 11 5 10 9 9 2 9 8 7 10 6 1 5 12 4 4 1 12 rows selected.
- 02下面的示例返回1998年。
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL; EXTRACT(YEARFROMDATE'1998-03-07') --------------------------------- 1998
- 03下面的示例从示例表hr.employees中选择,所有1998年以后入职的员工:
SELECT last_name, employee_id, hire_date FROM employees WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 1998 ORDER BY hire_date; LAST_NAME EMPLOYEE_ID HIRE_DATE ------------------------- ----------- --------- Landry 127 14-JAN-99 Lorentz 107 07-FEB-99 Cabrio 187 07-FEB-99 . . .
从当前时间,提取年、月、日。
select extract(year from sysdate) year,
extract(month from sysdate) month,
extract(day from sysdate) day
from dual;
- 04下面的例子导致歧义,因此Oracle返回UNKNOWN:最后这个如果不太理解,以后慢慢去理解好啦
SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 -08:00') FROM DUAL; EXTRACT(TIMEZONE_REGIONFROMTIMESTAMP'1999-01-0110:00:00-08:00') ---------------------------------------------------------------- UNKNOWN
产生歧义的原因是表达式中提供了时区数值偏移量,而该数值偏移量可以映射到多个时区区域。
以上内容,都是自己在日常学习时的总结,供参考,欢迎留言交流讨论。