Explanation of oracle interval function usage

Source: Internet
Author: User

I. interval function
The interval function is used to convert an integer value to the year, month, or hour, or second of the date type.

Oracle syntax:

Interval 'integer [-integer] '{YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} [(precision)] [TO {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}]

Note:
1. Year, month, day, hour, minute, and second can be used separately.
2. {YEAR | MONTH} to {YEAR | MONTH} and
{DAY | HOUR | MINUTE | SECOND} to {DAY | HOUR | MINUTE | SECOND} is used separately.
3. This function is often used to calculate a time difference.
4. precision is the precision field, valid range is 0 to 9, default value is 2. The precision here refers to the precision of the display result. If the result is longer than two characters, you must specify the precision.

Example:
1. 2 years

Select interval '2' year from dual;

INTERVAL '2' YEAR
---------------------------------------------------------------------------
+ 02-00

2. Indicates 1234 years

Select interval '000000' year (4) from dual;

INTERVAL '000000' YEAR (4)
---------------------------------------------------------------------------
+ 1234-00


3. Indicates 120 months.
1) no precision

Select interval '000000' month from dual;

INTERVAL '000000' MONTH
---------------------------------------------------------------------------
+ 10-00

2) precision

Select interval '000000' month (3) from dual;

INTERVAL '000000' MONTH (3)
---------------------------------------------------------------------------
+ 010-00

4. 2 days

Select interval '2' day from dual;

INTERVAL '2' DAY
---------------------------------------------------------------------------
+ 02 00:00:00

5. Time, minute, and second usage, and so on

6. Indicates a specific time (days to seconds)

Select interval '2 12:00:59 'day to second from dual;

INTERVAL '2014: 00: 59' DAYTOSECOND
---------------------------------------------------------------------------
+ 02 12:00:59. 000000

7. Indicates a specific time (year to month)

Select interval '21-10' year to month from dual;

INTERVAL '21-10' YEARTOMONTH
---------------------------------------------------------------------------
+ 21-10

II. numtodsinterval
The numtodsinterval function is used to convert an integer to a day, hour, minute, or second of the date type.

Syntax:

Numtodsinterval (n, 'interval _ unit ')

N is an integer, and interval_unit can be day, hour, minute, and second. This function cannot be converted to year or month.

Example:
1. 100 seconds

Select numtodsinterval (100, 'second') from dual;

NUMTODSINTERVAL (100, 'second ')
---------------------------------------------------------------------------
+ 000000000 00:01:40. 000000000

2. Indicates 200 minutes.

Select numtodsinterval (200, 'Minute ') from dual;

NUMTODSINTERVAL (200, 'Minute ')
---------------------------------------------------------------------------
+ 000000000 03:20:00. 000000000

3. 60 hours

Select numtodsinterval (60, 'hour') from dual;

NUMTODSINTERVAL (60, 'hour ')
---------------------------------------------------------------------------
+ 000000002 12:00:00. 000000000

4. 80 days

Select numtodsinterval (80, 'day') from dual;

NUMTODSINTERVAL (80, 'day ')
---------------------------------------------------------------------------
+ 000000080 00:00:00. 000000000

III. numtoyminterval
The numtoyminterval function is used to convert integers into years and months of the date type.

Syntax:

Numtoyminterval (n, 'interval _ unit ')

N is an integer, and interval_unit can be year or month.

Example:
1. Indicates January 1, 1234.

Select numtoyminterval (1234, 'year') from dual;

NUMTOYMINTERVAL (1234, 'year ')
---------------------------------------------------------------------------
+ 000001234-00

2. Indicates 120 months

Select numtoyminterval (120, 'month') from dual;

NUMTOYMINTERVAL (120, 'month ')
---------------------------------------------------------------------------
+ 000000010-00

From the example, we can see that the numtodsinterval function, numtoyminterval function, and interval function have the same effect after precision is added.

IV. Functions
So the question is, where are the functions of these time conversion functions?
If we want to calculate a value of the date type plus 100 seconds, 200 minutes, 80 hours, 60 days, 18 months, 21 years, we can use these functions for calculation.

Example:
1,

Select sysdate, sysdate + interval '20' day as res from dual;

SYSDATE RES
------------------------------
22-MAR-16 11-APR-16

2,

Select sysdate, sysdate + numtoyminterval (3, 'year') as res from dual;

SYSDATE RES
------------------------------
22-MAR-16 22-MAR-19

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.