ORACLE EXTRACT()

오라클 EXTRACT()

 

EXTRACT(datetime_filed FROM expr)

 

datetime_field 에는 

YEAR,

MONTH,

DAY,

HOUR,

MINUTE,

SECOND,

TIMEZONE_HOUR,

TIMEZONE_MINUTE,

TIMEZONE_REGION,

TIMEZONE_ABBR

이 올 수 있다. 

 

 

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the request filed:

 

• If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.
• If DAY is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, OR INTERVAL DAY TO SECOND.
• If HOUR, MINUTE, or SECOND is requested, then expr must evaluage to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which no time fields.
• If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZNONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested , then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAPM WITH LOCAL TIME ZONE.

 

This function can be very useful for manipulating datetime filed values in very large tables.

 

월별 주문건을  구할 때 유리할 것 같다.

음..2007년 이후 입사한 사람을 구할 때도.

 

SQL > SELECT EXTRACT(month FROM order_date) , COUNT(order_date) 
                   FROM orders
                   GROUP BY EXTRACT(month FROM order_date)
                   ORDER BY COUNT(order_date) DESC, EXTRACT(month FROM order_date);

 

 

SQL > SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL ;

   --> 1998

 

 

다만 이 쿼리를 TO_CHAR 로 어느정도 대체가 가능한 것 같다.

 

SQL > SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(DAY FROM SYSDATE) FROM DUAL ;

   --> 2020/03/30 오전 11:08:12       2020     3      30

 

SQL > SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'MM'), TO_CHAR(SYSDATE, 'DD') FROM DUAL ;

--> 2020/03/30 오전 11:10:01          2020     03    30