16.2 Interval Data Types & Arithmetic

16.2 Interval Data Types & Arithmetic

While datetime datatypes (DATE, TIMESTAMP, TSTZ, TSLTZ) represent specific points in time, enterprise systems frequently need to represent and manipulate spans or durations of elapsed time—such as warranty durations, employment tenure, subscription lengths, or process execution delays.

To represent elapsed time precisely without ambiguity, Oracle Database provides two dedicated interval datatypes: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

---

The Two Interval Datatype Families

Oracle separates intervals into two distinct, non-interchangeable datatype families because calendar years and months have variable numbers of days (28 to 31 days per month, 365 or 366 days per year), whereas days, hours, minutes, and seconds represent fixed, immutable units of physical time.

+-------------------------------------------------------------------------+
|                     ORACLE INTERVAL DATATYPE FAMILIES                   |
+-------------------------------------------------------------------------+
|                                                                         |
|   1. INTERVAL YEAR TO MONTH                                             |
|      - Measures elapsed spans in Years and Months                       |
|      - Syntax: INTERVAL YEAR [(year_precision)] TO MONTH                |
|      - Year Precision: 0 to 9 digits (Default = 2, i.e., 0-99 years)    |
|      - Month range: 0 to 11                                             |
|                                                                         |
|   2. INTERVAL DAY TO SECOND                                             |
|      - Measures elapsed spans in Days, Hours, Minutes, Secs, & Fract Sec|
|      - Syntax: INTERVAL DAY [(day_p)] TO SECOND [(fractional_sec_p)]    |
|      - Day Precision: 0 to 9 digits (Default = 2, i.e., 0-99 days)      |
|      - Fractional Sec Precision: 0 to 9 digits (Default = 6)            |
|                                                                         |
+-------------------------------------------------------------------------+

---

1. INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH stores a period of time expressed in years and months.

Column Definition Syntax

$\text{INTERVAL YEAR } [(\text{year\_precision})] \text{ TO MONTH}$

• year_precision: The maximum number of digits allowed in the leading year field (0 to 9). Default is 2 (supporting durations from $-99$ to $+99$ years).
• Months Field: Stored in the range 0 to 11. Any month value $\ge 12$ in literal expressions is automatically normalized into whole years and remaining months.

-- Create table tracking employee contracts and vesting periods
CREATE TABLE employee_contracts (
    contract_id      NUMBER(8) PRIMARY KEY,
    employee_id      NUMBER(6) NOT NULL,
    vesting_period   INTERVAL YEAR TO MONTH,         -- Default YEAR(2) TO MONTH
    max_tenure       INTERVAL YEAR(3) TO MONTH       -- Supports up to 999 years
);

Interval Year-to-Month Literal Syntax

Interval literals can be constructed using full hyphenated format or single-unit qualifiers:

-- 1. Full Year-Month literal: 5 years and 3 months
INTERVAL '5-3' YEAR TO MONTH

-- 2. Leading year precision required when years exceed 2 digits (e.g., 120 years, 6 months)
INTERVAL '120-6' YEAR(3) TO MONTH

-- 3. Negative duration: minus 2 years and 4 months
INTERVAL '-2-4' YEAR TO MONTH

-- 4. Single-unit literal: 18 months (automatically normalized to 1 year, 6 months)
INTERVAL '18' MONTH

-- 5. Single-unit literal: 15 years
INTERVAL '15' YEAR

Exam Trap: Leading Precision Error (ORA-01873) If an interval literal contains a leading value whose number of digits exceeds the specified (or default) leading precision, Oracle throws ORA-01873: the leading precision of the interval is too small.

-- FAILS with ORA-01873 because '150' has 3 digits but default precision is 2:
SELECT INTERVAL '150' YEAR FROM dual;

-- SUCCEEDS with explicit precision:
SELECT INTERVAL '150' YEAR(3) FROM dual;

---

2. INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND stores a period of time expressed in days, hours, minutes, seconds, and fractional seconds.

Column Definition Syntax

$\text{INTERVAL DAY } [(\text{day\_precision})] \text{ TO SECOND } [(\text{fractional\_seconds\_precision})]$

• day_precision: Maximum digits in the leading day field (0 to 9, default 2).
• fractional_seconds_precision: Digits in the fractional seconds field (0 to 9, default 6).

-- Create table tracking batch job execution times
CREATE TABLE batch_jobs (
    job_id       NUMBER(10) PRIMARY KEY,
    job_name     VARCHAR2(100) NOT NULL,
    run_duration INTERVAL DAY TO SECOND,                   -- Default DAY(2) TO SECOND(6)
    sla_limit    INTERVAL DAY(3) TO SECOND(3)              -- Up to 999 days, millisecond precision
);

Interval Day-to-Second Literal Syntax

-- 1. Full Day to Second literal: 4 days, 12 hours, 30 minutes, 15.5 seconds
INTERVAL '4 12:30:15.5' DAY TO SECOND

-- 2. Day to Hour literal: 10 days and 8 hours
INTERVAL '10 08' DAY TO HOUR

-- 3. Day to Minute literal: 2 days, 14 hours, and 45 minutes
INTERVAL '2 14:45' DAY TO MINUTE

-- 4. Hour to Minute literal: 36 hours and 15 minutes
INTERVAL '36:15' HOUR(2) TO MINUTE

-- 5. Minute to Second literal: 45 minutes and 30 seconds
INTERVAL '45:30' MINUTE TO SECOND

-- 6. Single-unit literals with precision
INTERVAL '120' DAY(3)
INTERVAL '500' HOUR(3)
INTERVAL '90' MINUTE
INTERVAL '45.123456' SECOND(2,6)

---

Comprehensive Interval Literal Syntax Reference

Exam Trap: Cannot Mix Families in a Single Interval Literal You cannot combine year/month and day/time units in a single interval literal. INTERVAL '1-2 03:04:05' YEAR TO SECOND is invalid SQL and throws ORA-00923 or ORA-01867.

---

Datetime and Interval Arithmetic Matrix

Oracle supports arithmetic operations combining datetimes (DATE, TIMESTAMP, TSTZ, TSLTZ), numbers, and intervals.

+-------------------------------------------------------------------------+
|                   DATETIME ARITHMETIC RULES & TYPES                     |
+-------------------------------------------------------------------------+
| Operation                  | Result Datatype      | Example Output      |
| :------------------------- | :------------------- | :------------------ |
| DATE + NUMBER              | DATE                 | DATE + 5 (Adds 5 d) |
| DATE - NUMBER              | DATE                 | DATE - 1 (Sub 1 d)  |
| DATE - DATE                | NUMBER               | 2.5 (2.5 days)      |
| DATE + INTERVAL YTM        | DATE                 | DATE + 1 Year       |
| DATE + INTERVAL DTS        | DATE                 | DATE + 3 Hours      |
| TIMESTAMP + INTERVAL YTM   | TIMESTAMP            | TS + 6 Months       |
| TIMESTAMP + INTERVAL DTS   | TIMESTAMP            | TS + 15.5 Seconds   |
| TIMESTAMP - TIMESTAMP      | INTERVAL DAY TO SEC  | +02 04:30:00.000000 |
| TIMESTAMP + NUMBER         | DATE (implicit conv) | Fract secs + TZ lost|
+-------------------------------------------------------------------------+

Critical 1Z0-071 Distinction: DATE - DATE vs. TIMESTAMP - TIMESTAMP

This is one of the most heavily tested concepts on the 1Z0-071 exam:

-- 1. Subtracting two DATE values yields a NUMBER (representing fractional days):
SELECT TO_DATE('2026-08-15 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - 
       TO_DATE('2026-08-15 06:00:00', 'YYYY-MM-DD HH24:MI:SS') AS date_diff
FROM dual;
-- Result: 0.5 (A numeric value indicating half a day)

-- 2. Subtracting two TIMESTAMP values yields an INTERVAL DAY TO SECOND:
SELECT TO_TIMESTAMP('2026-08-15 18:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') - 
       TO_TIMESTAMP('2026-08-15 06:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') AS ts_diff
FROM dual;
-- Result: +00 12:00:00.000000000 (An INTERVAL DAY TO SECOND datatype)

Exam Trap: TIMESTAMP + NUMBER Silently Degrades to DATE Adding a number to a DATE adds days (SYSDATE + 1 is tomorrow). Adding a number to a TIMESTAMP is not rejected — Oracle implicitly converts the timestamp to a DATE first, so SYSTIMESTAMP + 1 returns a DATE and silently discards the fractional seconds and the time zone. To add a duration to a timestamp and keep the result a timestamp, add an interval instead: SYSTIMESTAMP + INTERVAL '1' DAY.

---

Month-End Boundary Traps: ADD_MONTHS vs. INTERVAL YEAR TO MONTH

A critical distinction exists in how Oracle handles end-of-month dates when adding months:

-- Case 1: Using ADD_MONTHS on January 31:
SELECT ADD_MONTHS(DATE '2026-01-31', 1) FROM dual;
-- Result: 2026-02-28 (ADD_MONTHS automatically clamps to the last day of February)

-- Case 2: Using INTERVAL arithmetic on January 31:
SELECT DATE '2026-01-31' + INTERVAL '1' MONTH FROM dual;
-- FAILS with ORA-01839: date not valid for month specified

Why ORA-01839 Occurs

Interval addition performs strict calendar math without automatic day-clamping. Adding 1 month to January 31 yields February 31, 2026. Because February 31 is an invalid calendar date, Oracle throws ORA-01839. In contrast, ADD_MONTHS possesses built-in month-end preservation logic that safely adjusts to February 28 (or 29 in a leap year).