Menu

Oracle INTERVAL DAY TO SECOND: Precision, Syntax, and Examples

Oracle INTERVAL DAY TO SECOND stores a duration in days, hours, minutes, seconds, and optional fractional seconds.

It is commonly returned when subtracting two TIMESTAMP values.

Syntax

INTERVAL DAY [(day_precision)]
  TO SECOND [(fractional_seconds_precision)]

The two precision values control different parts of the interval.

day_precision

The number of digits allowed in the leading DAY field.

  • allowed range: 0 to 9
  • default: 2

fractional_seconds_precision

The number of digits allowed after the decimal point in the SECOND field.

  • allowed range: 0 to 9
  • default: 6

For example:

INTERVAL DAY(4) TO SECOND(9)

allows up to four digits in the day field and up to nine fractional-second digits.

Default Precision

This declaration:

duration INTERVAL DAY TO SECOND

uses day precision 2 and fractional-second precision 6.

The previous version of this page incorrectly stated that the default fractional-second precision was 0. Oracle’s documented default is 6.

Create a Table

CREATE TABLE job_run (
  id NUMBER PRIMARY KEY,
  duration INTERVAL DAY(3) TO SECOND(6)
);

Insert an interval literal:

INSERT INTO job_run
VALUES (
  1,
  INTERVAL '2 03:12:45.123456' DAY TO SECOND
);

Subtract Two Timestamps

Subtracting one TIMESTAMP from another returns an interval:

SELECT
  TIMESTAMP '2026-10-08 09:30:00'
  - TIMESTAMP '2026-10-07 12:00:00'
  AS duration
FROM dual;

The result represents 21 hours and 30 minutes.

Convert an Interval to Total Seconds

Oracle does not automatically flatten an INTERVAL DAY TO SECOND into one numeric unit. Extract each component and combine them:

WITH x AS (
  SELECT
    TIMESTAMP '2026-10-08 09:30:00'
    - TIMESTAMP '2026-10-07 12:00:00'
    AS duration
  FROM dual
)
SELECT
    EXTRACT(DAY FROM duration) * 86400
  + EXTRACT(HOUR FROM duration) * 3600
  + EXTRACT(MINUTE FROM duration) * 60
  + EXTRACT(SECOND FROM duration) AS total_seconds
FROM x;

Fractional Seconds

To explicitly request nine fractional-second digits:

CREATE TABLE timing_test (
  duration INTERVAL DAY(2) TO SECOND(9)
);

A value such as:

INTERVAL '0 00:00:00.000000001' DAY TO SECOND

can preserve nine fractional-second digits within the declared precision.

NUMTODSINTERVAL

When you start with a number of days, hours, minutes, or seconds, use NUMTODSINTERVAL():

SELECT NUMTODSINTERVAL(90, 'SECOND')
FROM dual;

This returns an INTERVAL DAY TO SECOND value representing 1 minute and 30 seconds.

INTERVAL vs DATE Arithmetic

Subtracting two Oracle DATE values returns a number of days.

Subtracting two TIMESTAMP values returns an INTERVAL DAY TO SECOND.

Summary

  • INTERVAL DAY TO SECOND stores a duration, not a point in time.
  • Default day precision is 2.
  • Default fractional-second precision is 6.
  • Both precision values can range from 0 through 9.
  • Timestamp subtraction returns a day-to-second interval.
  • Use EXTRACT() to convert fields to a total numeric unit.
  • Use NUMTODSINTERVAL() to create an interval from a number.

See Oracle’s Data Types documentation.