Oracle extract

Definition:
There are two EXTRACT functions available in Oracle. They both work with inputs of different types and return output of different types. 

1. The Oracle PL/SQL EXTRACT(date time) function extracts a value from a date or interval value.
2. The Oracle PL/SQL EXTRACT(XML) function is used to select a node (or set of nodes) along with its leaf nodes.

EXTRACT(date time)

The Oracle EXTRACT(Date Time) function splits a datetime or interval expression and returns the value of the specified datetime field (YEAR, MONTH, DAY from DATE and TIMEZONE_HOUR, TIMEZONE_MINUTE from TIMEZONE). It is useful while working with datetime values in tables with large volumes of data.

Example Syntax:

EXTRACT (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

| { TIMEZONE_HOUR | TIMEZONE_MINUTE }

| { TIMEZONE_REGION | TIMEZONE_ABBR }

FROM { date_value | interval_value } )

Example Usage:

SELECT EXTRACT(YEAR FROM DATE '2010-01-12') FROM DUAL;



EXTRACT(YEARFROMDATE'2010-01-12')

---------------------------------

2010

SELECT EXTRACT(DAY FROM DATE '2003-08-22') FROM DUAL;



EXTRACT(DAYFROMDATE'2010-01-12')

---------------------------------

12

The SQL query below returns the month that was current (in effect) 40 days ago:

SELECT EXTRACT(MONTH FROM SYSDATE-40) FROM DUAL;

EXTRACT(xml)

The EXTRACT(XML) function is used to select a node (or set of nodes) along with its leaf nodes. It takes two parameters, object_value and XPath, and returns XMLType output. The XPath parameter can be an absolute or relative type.

Example Syntax:

EXTRACT(xml-type-instance, 'xpath')

EXTRACT(xml-type-instance, 'xpath', 'namespace')

Example Usage:

SQL> SELECT EXTRACT(OBJECT_VALUE, '/Orders/MailAddressTo')

FROM ORDERS_XML;



EXTRACT(OBJECT_VALUE,'/ORDERS/MAILADDRESSTO')

------------------------------------------------------------------------------

<MailAddressTo id="PA"><Company>Google</Company>

<Country>United States</Country>

<City>NewYork</City><State>NY</State><Zipcode>12345</Zipcode>

</MailAddressTo>



SQL> SELECT EXTRACT(OBJECT_VALUE,  './/Company')

FROM ORDERS_XML;



EXTRACT(OBJECT_VALUE,'.//COMPANY')

---------------------------------------------------------

<Company>Google</Company><Company>PSOUG</Company>

examples for extract hour, minutes, seconds

SELECT (EXTRACT (DAY FROM (end_ts-start_ts))*24*60*60+
EXTRACT (HOUR FROM (end_ts-start_ts))*60*60+
EXTRACT (MINUTE FROM (end_ts-start_ts))*60+
EXTRACT (SECOND FROM (end_ts-start_ts)))/60
FROM TS;

sql> select to_char(sysdate, 'hh24:mi:ss') "Current Time",
  2     extract(hour from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Hour",
  3     extract(minute from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Minute",
  4     extract(second from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Second"
  5     from dual;

 
SQL> select to_char(sysdate, 'hh24:mi:ss') "Current Time",
  2     to_char(sysdate, 'hh24') "Hour",
  3     to_char(sysdate, 'mi') "Minute",
  4     to_char(sysdate, 'ss') "Second"
  5     from dual;