Explanation of oracle interval function usage

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