Oracle Extract () function----used to intercept years, months, days, hours, minutes, seconds

Oracle's extract () function is introduced from Oracle 9i to intercept a specific part from a date or interval type

The syntax is as follows:

Extract (            year| Month |  Day | | | Second}             | | Timezone_minute}             | | Timezone_abbr}      from | Interval_value})

You can only intercept dates from a date type.

Sql> SelectExtract ( Year  fromSysdate) Year, Extract (Month  fromSysdate)Month, Extract ( Day  fromSysdate) Day fromdual;  Year      MONTH         Day---------- ---------- ----------       -          5          4

只可以从一个date类型中截取年月日

Sql> SelectExtract ( Year  fromDate'2015-05-04') Year, Extract (Month  fromDate'2015-05-04')Month, Extract ( Day  fromDate'2011-05-04') Day  fromdual;  Year      MONTH         Day---------- ---------- ----------       -          5          4

Timestamp from the day of the month

SelectExtract ( Year  fromSystimestamp) Year, Extract (Month  fromSystimestamp)Month, Extract ( Day  fromSystimestamp) Day, extract (minute fromSystimestamp) minute,extract (second fromsystimestamp) second,extract (Timezone_hour fromsystimestamp) th,extract (Timezone_minute fromsystimestamp) tm,extract (timezone_region fromsystimestamp) tr,extract (timezone_abbr fromsystimestamp) Ta fromDual

Get a specific time interval between two dates

Get a specific time interval between two dates, the Extract function is the best choice

SelectExtract ( Day  fromDt2-DT1) Day, extract (Hour fromDt2-dt1) hour,extract (minute fromDt2-dt1) minute,extract (second fromDt2-dt1) Second from(SelectTo_timestamp ('2011-02-04 15:07:00','YYYY-MM-DD Hh24:mi:ss') Dt1,to_timestamp ('2011-05-17 19:08:46','YYYY-MM-DD Hh24:mi:ss') DT2 fromdual)

Get interval Type specific parts

Select Extract (year from"year" Year from dual

Interval syntax

 interval  " {integer | integer time_expr | time_ Expr}   "  {{  Day  |  hour |  minute} [  (leading_precision)  ]   |  second [  (Leading_precision [, fractional_seconds_precision  ]  )]}  [  to {day | hour | minute | secon d [(fractional_seconds_precision)  ] }] 

The range of leading_precision values is 0 to 9, and the default is 2. TIME_EXPR in the format: HH[:MI[:SS[.N]]] or MI[:SS[.N]] or SS[.N], N for microseconds.

Range Value:

Hour:0 to 23

Minute:0 to 59

second:0 to 59.999999999

eg

Interval ' 4 5:12:10.222 ' Day to second (3) means: 4 days 5 hours 12 minutes 10.222 seconds

Interval ' 4 5:12 ' day to Minute said: 4 days 5 hours 12 minutes

Interval ' 5 ' Day (3) to hour means: 400 days 5 hours, 400 is 3 for precision, so "day (3)", note the default value is 2.

Interval ' + ' Day (3) means: 400 days

Interval ' 11:12:10.2222222 ' Hour to second (7) means: 11 hours, 12 minutes, 10.2222222 seconds

Interval ' 11:20 ' hour to minute: 11 hours 20 minutes

Interval ' hour says: 10 hours

Interval ' 10:22 ' minute to second: 10 minutes 22 seconds

Interval ' minute says: 10 points

Interval ' 4 ' Day means: 4 days

Interval ' + ' hour says: 25 hours

Interval ' + ' minute says: 40 points

Interval ' hour (3) means: 120 hours

Interval ' 30.12345 ' second (2,4) means: 30.1235 seconds, because the location of the seconds after the precision is set to 4, to be rounded.

Interval ' day-interval ' hour = Interval ' 10-0 ' Day to second means: 20 days-240 hours = 10 days 0 seconds

Oracle Extract () function----used to intercept years, months, days, hours, minutes, seconds