Oracle Extract Functions

Oracle's Extract function // The extract () function in oracle is introduced from oracle 9i. It is used to Extract a specific part from a date or interval type. // The syntax is as follows:

EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} | {TIMEZONE_HOUR | TIMEZONE_MINUTE} | {TIMEZONE_REGION | TIMEZONE_ABBR} FROM {date_value | interval_value }) // We can only extract year, month, and day from a date type (the date format is yyyy-mm-dd ); // We can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with time zone data type; select extract (year from date '2017-05-17 ') year from dual; YEAR ---------- 2011 select extract (month from date '2014-05-17 ') month from dual; MONTH ---------- 5 select extract (day from date '2014-05-17') day from dual; DAY ---------- 17 // get the exact interval between two dates, the extract function is the best choice for select extract (day from dt2-dt1) day, extract (hour from dt2-dt1) hour, extract (minute from dt2-dt1) minute, extract (second from dt2-dt1) second from (select to_timestamp ('2017-02-04 15:07:00 ', 'yyyy-mm-dd hh24: mi: ss ') dt1, to_timestamp ('2017-05-17 19:08:46', 'yyyy-mm-dd hh24: mi: ss') dt2 from dual) /day hour minute second ---------- 102 4 1 46 -- select extract (year from week imestamp) year, extract (month from week imestamp) month, extract (day from week imestamp), day, extract (minute from Alibaba imestamp) minute, extract (second from Alibaba imestamp) second, extract (distinct from nation) th, extract (distinct from Alibaba imestamp) tm, extract (distinct from Alibaba imestamp, extract (distinct from begin imestamp) ta from dual/year month day minute second th tm tr ta ---------- ----------- ---------- 2011 5 17 7 14.843 8 0 unknown unk //