Oracle Interval Functions Guide, FAQ & Examples

Oracle’s two INTERVAL data types are used to store a period of time. There are a few functions that convert data into these data types. Learn how to use them and see some examples in this article.

What Are the Oracle INTERVAL Functions?

There are four INTERVAL functions that I’ll cover in this article.

Here’s the functions and a short description of what they do:

• NUMTOYMINTERVAL: Converts a number to an INTERVAL TO YEAR MONTH value.
• NUMTODSINTERVAL: Converts a number to an INTERVAL TO DAY SECOND value.
• TO_YMINTERVAL: Converts a character to an INTERVAL TO YEAR MONTH value.
• TO_DSINTERVAL: Converts a character to an INTERVAL TO DAY SECOND value.

Yes, two of the functions have an underscore, and two don’t.

What’s an INTERVAL data type? Why are there two of them? I’ve covered them in this article.

An INTERVAL YEAR TO MONTH data type lets you store a period of time by specifying the years and months. An INTERVAL DAY TO SECOND data type lets you store a period of time by specifying the days, hours, minutes and seconds.

 

NUMTOYMINTERVAL Syntax and Parameters

The syntax of the NUMTOYMINTERVAL function is:

NUMTOYMINTERVAL ( number, interval_unit )

The parameters of the NUMTOYMINTERVAL function are:

• number (mandatory): This is the number to be converted to the interval type.
• interval_unit (mandatory): This is the unit of the interval to use. It is a character type and can be either of ‘YEAR’ or ‘MONTH’.

Some other things to know about this function are:

• The number parameter can be any NUMBER type, or a value that can be implicitly converted to a NUMBER.
• The interval_unit can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2 types.
• The interval_unit parameter is case insensitive. Leading and trailing values are ignored.
• By default, the precision of the returned value is 9.

 

NUMTODSINTERVAL Syntax and Parameters

The syntax of the NUMTODSINTERVAL function is:

NUMTODSINTERVAL ( number, interval_unit )

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

The parameters of the NUMTODSINTERVAL function are:

• number (mandatory): This is the number to be converted to an interval.
• interval_unit (mandatory): This is the unit of the interval to use. It is a character type and can be either DAY, HOUR, MINUTE, or SECOND

Some notes about this function:

• The number parameter can be any NUMBER type, or a value that can be implicitly converted to a NUMBER.
• The interval_unit can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2 types.
• The interval_unit parameter is case insensitive. Leading and trailing values are ignored.
• By default, the precision of the returned value is 9.

 

TO_YMINTERVAL Syntax and Parameters

The syntax for the Oracle TO_YMINTERVAL function is:

TO_YMINTERVAL ( input_string )

The parameters of the TO_YMINTERVAL function are:

• input_string (mandatory): This is the value that is to be converted to an INTERVAL YEAR TO MONTH data type.

The input string can be in one of two formats:

• A character string in an interval format (e.g. “02-05”) which can be any of CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
• An ISO duration format

We’ll see an example of both formats later in this article.

 

TO_DSINTERVAL Syntax and Parameters

The syntax for the TO_DSINTERVAL function is quite simple:

TO_DSINTERVAL ( input_string [, nlsparam] )

The parameters of the TO_DSINTERVAL function are:

• input_string (mandatory): The character string to be converted into an INTERVAL DAY TO SECOND data type.
• nlsparam (optional): A string value that represents the decimal and group characters. It must be a NLS_NUMERIC_CHARACTERS value.

Some things to note about this function:

• The input_string can be any of CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
• The only valid nlsparam for this function is NLS_NUMERIC_CHARACTERS.

 

TO_YMINTERVAL and the ISO Duration Format

This function also accepts input in the format of the ISO duration.

This can be specified as:

[ – ] P [ years Y] [ months M ] [ days D ]

Some examples of this would be:

• “-P2Y6M”
• “P12Y3M”
• “P0Y8M”

 

How Can I Specify or Change the Oracle NUMTODSINTERVAL Format?

The output format of the Oracle NUMTODSINTERVAL function isn’t always what you want. It’s returned as an INTERVAL DAY TO SECOND type, which would display something like this:

+000000003 04:08:19.000000000

How can you format this output and turn it into something else?

You’ll need to use a combination of string manipulation functions and the EXTRACT function.

See the Examples section below for some examples on how to format the output of a NUMTODSINTERVAL function.

 

Can You Use NUMTODSINTERVAL with Milliseconds?

Yes, you can.

To do this, specify a decimal value for the number (e.g. 0.25) and the ‘SECOND’ value for the interval unit. This will add 250 milliseconds to the interval data type.

See the Examples section below for more information.

 

Can You Use NUMTODSINTERVAL with a Month?

No, you can’t use a month with this function.

This function returns an INTERVAL DAY TO SECOND data type. The maximum part of this time value is a DAY.

How would you convert a number of days into a month? How many days are in a month? 28, 29, 30 or 31? When you’re talking about a generic month, Oracle doesn’t know.

So, to use a month value with a function, try the NUMTOYMINTERVAL function.

 

NUMTOYMINTERVAL Function Examples

Here are some examples of the NUMTOYMINTERVAL function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – Year

This example uses an input value and an interval type of “year”.

SELECT NUMTOYMINTERVAL(15, 'YEAR') AS YMTEST
FROM dual;

Result:

YMTEST

+15-00

The value shown is +15-00, which represents a positive value of 15 years and 0 months.

Example 2 – Month

This example uses an input value and an interval type of “month”.

SELECT NUMTOYMINTERVAL(23, 'MONTH') AS YMTEST
FROM dual;

Result:

YMTEST

+01-11

The value shown is +01-11, which represents a positive value of 1 year and 11 months. This is because 23 months is converted to 1 year and 11 months.

Example 3 – Negative Years

This example uses an input value and an interval type of “year”, which is negative.

SELECT NUMTOYMINTERVAL(-4, 'YEAR') AS YMTEST
FROM dual;

Result:

YMTEST

-04-00

This value is shown as -4-00 which represents a negative value of 4 years.

Example 4 – Negative Months

This example uses an input value and an interval type of “month”, which is negative.

SELECT NUMTOYMINTERVAL(-18, 'MONTH') AS YMTEST
FROM dual;

Result:

YMTEST

-01-06

This value represents a negative value of 01-06, which is 1 year and 6 months.

 

NUMTODSINTERVAL Function Examples

Here are some examples of the Oracle NUMTODSINTERVAL function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – Day

This example converts a DAY number to an INTERVAL DAY TO SECOND data type.

SELECT NUMTODSINTERVAL(14, 'DAY') AS numtest
FROM dual;

Result:

NUMTEST

+14 00:00:00.000000

The +14 indicates that this is positive 14 days. If it is added to a date, it will move the date into the future.

Example 2 – Hour

This example converts an HOUR number to an INTERVAL DAY TO SECOND data type.

SELECT NUMTODSINTERVAL(6, 'HOUR') AS numtest
FROM dual;

Result:

NUMTEST

+00 06:00:00.000000

The number 8 is shown in the result as 8 hours.

Example 3 – More Than 24 Hours

This example shows what happens if you specify a number greater than the number of hours in a day

SELECT NUMTODSINTERVAL(32, 'HOUR') AS numtest
FROM dual;

Result:

NUMTEST

+01 08:00:00.000000

As you can see, it has converted this into 1 day and 8 hours, because that’s what 32 hours converts to.

Example 4 – Minute

This example converts a MINUTE number to an INTERVAL DAY TO SECOND data type.

SELECT NUMTODSINTERVAL(25, 'MINUTE') AS numtest
FROM dual;

Result:

NUMTEST

+00 00:25:00.000000

This shows 25 minutes in the data type.

Example 5 – High Number of Minutes

This example uses a high number of minutes and converts it to the INTERVAL DAY TO SECOND data type.

SELECT NUMTODSINTERVAL(412, 'MINUTE') AS numtest
FROM dual;

Result:

NUMTEST

+00 06:52:00.000000

It has been converted to 6 hours and 52 minutes.

Example 6 – Second

This example converts a SECOND number to an INTERVAL DAY TO SECOND data type.

SELECT NUMTODSINTERVAL(51, 'SECOND') AS numtest
FROM dual;

Result:

NUMTEST

+00 00:00:51.000000

It shows a value of 51 seconds.

Example 7 – Very High Number of Seconds

This example shows what happens if you use a very high number of seconds.

SELECT NUMTODSINTERVAL(8941564, 'SECOND') AS numtest
FROM dual;

Result:

NUMTEST

+103 11:46:04.000000

This has been converted into 103 days, 11 hours, 46 minutes and 4 seconds.

Example 8 – Decimal Seconds or Milliseconds

This example uses a decimal number to convert a number into an INTERVAL DAY TO SECOND data type.

SELECT NUMTODSINTERVAL(16.265497, 'SECOND') AS numtest
FROM dual;

Result:

NUMTEST

+00 00:00:16.265497

As you can see, any decimal places have been converted to milliseconds. This works the same with any interval_unit specified.

Example 9 – Negative Input

This example shows what happens when we use a negative number.

SELECT NUMTODSINTERVAL(-18, 'HOUR') AS numtest
FROM dual;

Result:

NUMTEST

-00 18:00:00.000000

This has been displayed as 18 hours. Notice the “-” sign at the start. This indicates that it is a negative interval, so when it is added to dates, it subtracts the value.

Example 10 – Formatting the Output

This example shows how to format the output of the NUMTODSINTERVAL function.

SELECT
NUMTODSINTERVAL(8941564.59401,'SECOND') AS original_value,
  TO_CHAR
     ( LPAD(EXTRACT(DAY    FROM elapsed_interval),3,'0')||' '||
       LPAD(EXTRACT(HOUR   FROM elapsed_interval),2,'0')||':'||
       LPAD(EXTRACT(MINUTE FROM elapsed_interval),2,'0')||':'||
       LPAD(EXTRACT(SECOND FROM elapsed_interval),8,'0')
     ) AS formatted_seconds
 FROM (
   SELECT numtodsinterval(8941564.59401,'SECOND') AS elapsed_interval
   FROM dual
 );

Result:

ORIGINAL_VALUE	FORMATTED_SECONDS
+103 11:46:04.594010	103 11:46:04.59401

This query has a few steps in it. I’ve selected the original unformatted value so you can see what it looks like.

Next, I extracted each of the day, hour, minute, and second from the interval value. This interval value is called elapsed_interval and was selected later in the query, which you can substitute for your own value.

Then, the extracted values were padded with 0’s using LPAD up to a specified length. Then, they were concatenated with spaces or colons.

Finally, it was converted to a character value using TO_CHAR.

This method is quite flexible and you can change the padding and other values as needed.

 

TO_DSINTERVAL Function Examples

Here are some examples of the TO_DSINTERVAL function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This is a basic example of the TO_DSINTERVAL function.

SELECT TO_DSINTERVAL('120 6:31:14') AS output
FROM dual;

Result:

OUTPUT

+120 06:31:14.000000

The output is shown here in an INTERVAL DAY TO SECOND data type.

Example 2

Here’s another basic example of the function.

SELECT TO_DSINTERVAL('3 14:09:32') AS output
FROM dual;

Result:

OUTPUT

+03 14:09:32.000000

The output is shown here in an INTERVAL DAY TO SECOND data type.

Example 3

This example uses a value of 0 for the days.

SELECT TO_DSINTERVAL('0 2:06:00') AS output
FROM dual;

Result:

OUTPUT

+00 02:06:00.000000

The output is shown here in an INTERVAL DAY TO SECOND data type.

 

TO_YMINTERVAL Function Examples

Here are some examples of the TO_YMINTERVAL function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This is a basic example on how to use the Oracle TO_YMINTERVAL function.

SELECT TO_YMINTERVAL('02-05') AS INTERVAL_TEST
FROM dual;

Result:

INTERVAL_TEST

+02-05

It shows an INTERVAL value which is 2 years and 5 months in the future.

Example 2

This is another basic example with higher values.

SELECT TO_YMINTERVAL('15-06') AS INTERVAL_TEST
FROM dual;

Result:

INTERVAL_TEST

+15-06

It shows an INTERVAL value which is 15 years and 6 months in the future.

Example 3

This example adds an interval to a date value.

SELECT SYSDATE, SYSDATE + TO_YMINTERVAL('4-01') AS INTERVAL_TEST
FROM dual;

Result:

SYSDATE	INTERVAL_TEST
3/MAR/16	3/APR/20

This shows today’s date, and then a date which is 4 years and 1 month in the future.

Example 4

This example uses the ISO input type.

SELECT TO_YMINTERVAL('P1Y2M') AS INTERVAL_TEST
FROM dual;

Result:

INTERVAL_TEST

+01-02

This result is 1 year and 2 months in the future.

 

You can find a full list of Oracle SQL functions here.