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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.