Menu

Oracle NUMTODSINTERVAL: Return Type, Precision, and Examples

Oracle NUMTODSINTERVAL() converts a number to an INTERVAL DAY TO SECOND value. It does not return text.

Syntax

NUMTODSINTERVAL(n, 'interval_unit')

Parameters

n

A NUMBER value, or an expression Oracle can implicitly convert to NUMBER.

interval_unit

One of DAY, HOUR, MINUTE, or SECOND. The value is case-insensitive.

Return Type and Precision

The return value is an INTERVAL DAY TO SECOND.

Oracle documents the default precision of the returned interval as 9. In practice, NUMTODSINTERVAL() can return a day-to-second interval with nine-digit leading-day precision and high fractional-second precision.

Do not confuse this with a column declared simply as:

INTERVAL DAY TO SECOND

For that datatype declaration, the default day precision is 2 and the default fractional-second precision is 6.

Days

SELECT NUMTODSINTERVAL(3, 'DAY') AS duration
FROM dual;

This represents three days.

Hours

SELECT NUMTODSINTERVAL(30, 'HOUR') AS duration
FROM dual;

Oracle normalizes the value to one day and six hours.

Fractional Seconds

SELECT NUMTODSINTERVAL(4.123456789, 'SECOND') AS duration
FROM dual;

The fractional part is preserved as fractional seconds.

Add Seconds to a Timestamp

SELECT
  TIMESTAMP '2026-10-07 12:00:00'
  + NUMTODSINTERVAL(90, 'SECOND') AS result
FROM dual;

The result represents 2026-10-07 12:01:30.

Using an interval keeps timestamp arithmetic explicit and avoids treating the number as a count of days.

NULL Arguments

If an argument evaluates to NULL, the result is NULL:

SELECT
  NUMTODSINTERVAL(NULL, 'SECOND'),
  NUMTODSINTERVAL(1, NULL)
FROM dual;

NUMTODSINTERVAL vs TO_DSINTERVAL

Use NUMTODSINTERVAL() when the duration starts as a numeric value.

Use TO_DSINTERVAL() when the duration starts as a character representation of an interval.

Summary

  • NUMTODSINTERVAL() returns an INTERVAL DAY TO SECOND, not text.
  • Supported units are DAY, HOUR, MINUTE, and SECOND.
  • Oracle documents a default return precision of 9.
  • Fractional seconds are supported.
  • The function is useful for adding numeric durations to timestamps.

See Oracle’s NUMTODSINTERVAL documentation.