ExamFinch.com My Study

Study guide

16.2 Interval Data Types & Arithmetic

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

16.2 Interval Data Types & Arithmetic

Key Takeaways Explain This Topic AI

Oracle provides two interval datatypes: INTERVAL YEAR TO MONTH (storing spans of years and months) and INTERVAL DAY TO SECOND (storing spans of days, hours, minutes, seconds, and fractional seconds).

The default leading precision for both interval types is 2 digits (representing up to 99 years or 99 days), with a maximum configurable precision of 9 digits.

Interval literals require explicit leading precision (e.g., INTERVAL '120' YEAR(3)) if the literal value contains more digits than the default 2.

Subtracting two DATE values produces a NUMBER representing the difference in fractional days; subtracting two TIMESTAMP values produces an INTERVAL DAY TO SECOND.

Adding an INTERVAL YEAR TO MONTH to a month-end date can raise ORA-01839 if the resulting day does not exist in the target month, whereas ADD_MONTHS automatically clamps the result to the last day of the month.

Last updated: August 2026

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 INTERVAL YEAR [ ( year_precision ) ] TO MONTH \text{INTERVAL YEAR } [(\text{year\_precision})] \text{ TO MONTH} INTERVAL YEAR [( year_precision )] 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 -99 − 99 to + 99 +99 + 99 years).

Months Field: Stored in the range 0 to 11 . Any month value ≥ 12 \ge 12 ≥ 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 INTERVAL DAY [ ( day_precision ) ] TO SECOND [ ( fractional_seconds_precision ) ] \text{INTERVAL DAY } [(\text{day\_precision})] \text{ TO SECOND } [(\text{fractional\_seconds\_precision})] INTERVAL DAY [( day_precision )] TO SECOND [( 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
  • Literal Syntax Example Datatype Family Normalized Value Notes / Exam Rules
  • INTERVAL '3-6' YEAR TO MONTH YEAR TO MONTH +03-06 (3 yrs, 6 mos) Default leading precision YEAR(2)
  • INTERVAL '105-2' YEAR(3) TO MONTH YEAR TO MONTH +105-02 (105 yrs, 2 mos) YEAR(3) required for 3-digit year
  • INTERVAL '24' MONTH YEAR TO MONTH +02-00 (2 yrs, 0 mos) Month normalized to 2 whole years
  • INTERVAL '5' YEAR YEAR TO MONTH +05-00 (5 yrs, 0 mos) Single-unit year specification
  • INTERVAL '4 12:30:00' DAY TO SECOND DAY TO SECOND +04 12:30:00.000000 Full day-to-second format
  • INTERVAL '100 00:00:00' DAY(3) TO SECOND DAY TO SECOND +100 00:00:00.000000 DAY(3) required for 100 days
  • INTERVAL '36' HOUR DAY TO SECOND +01 12:00:00.000000 36 hours normalized to 1 day, 12 hrs
  • INTERVAL '90' MINUTE DAY TO SECOND +00 01:30:00.000000 90 mins normalized to 1 hr, 30 mins
  • INTERVAL '120.5' SECOND(3,1) DAY TO SECOND +00 00:02:00.500000 120.5s normalized to 2 mins, 0.5s

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).