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