Oracle INTERVAL Data Type

Summary: in this tutorial, you’ll learn about the Oracle INTERVAL data types and how to handle intervals effectively.

Introduction to Oracle interval data type #

In Oracle, the INTERVAL data type allows you to store a specific period of time between two date time values. In other words, when you subtract two date time values, you’ll get an interval.

Oracle supports two types of intervals:

INTERVAL YEAR TO MONTH – stores intervals using year and month.
INTERVAL DAY TO SECOND – stores intervals using days, hours, minutes, and seconds including fractional seconds.

INTERVAL YEAR TO MONTH #

The INTERVAL YEAR TO MONTH data type represents a period in years and month such as 1 year 3 months.

Here’s the syntax of INTERVAL YEAR TO MONTH data type:

INTERVAL YEAR [(year_precision)] TO MONTHCode language: SQL (Structured Query Language) (sql)

The year_precision represents the number of digits in the YEAR field. It ranges from 0 to 9. It means you can use the INTERVAL YEAR TO MONTH data type to store up to 999,999,999 years.

The year_precision is optional. If you omit the year_precision argument, it defaults to 2. In other words, you can store up to a period of 99 years and 11 months by default, which must be less than 100 years.

INTERVAL YEAR TO MONTH literals #

To define literal values for the INTERVAL YEAR TO MONTH data type, you use the following format:

INTERVAL 'year[-month]' YEAR(precision) TO MONTHCode language: SQL (Structured Query Language) (sql)

In this syntax:

'year[-month]' is a string that represent the year and month for the leading and trailing fields of the interval.

For example, the following represents an interval of 10 years 2 months:

INTERVAL '10-2' YEAR TO MONTHCode language: JavaScript (javascript)

precision is the maximum number of digits in the year field. The precision ranges from 0 to 9. The default value is 2.

The following table illustrates examples of INTERVAL YEAR TO MONTH literals:

INTERVAL YEAR TO MONTH Literals	Meaning
`INTERVAL '120-3' YEAR(3) TO MONTH`	An interval of 120 years, 3 months; Must specify the leading field precision `YEAR(3)` because the value of the leading field is greater than the default precision (2 digits).
`INTERVAL '105' YEAR(3)`	An interval of 105 years 0 months.
`INTERVAL '9' YEAR`	9 years, which is equivalent to `INTERVAL '9-0' YEAR TO MONTH`
`INTERVAL '40' MONTH`	40 months or 3 years 4 months, which is equivalent to `INTERVAL '3-4' YEAR TO MONTH`
`INTERVAL '180' YEAR`	Invalid interval because ‘180’ has 3 digits which are greater than the default precision (2)

Oracle INTERVAL YEAR TO MONTH example #

First, create a new table calledcandidates for the demonstration:

CREATE TABLE candidates ( candidate_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, first_name VARCHAR2 (50) NOT NULL, last_name VARCHAR2 (50) NOT NULL, job_title VARCHAR2 (255) NOT NULL, year_of_experience INTERVAL YEAR TO MONTH );Code language: SQL (Structured Query Language) (sql)

In this table, we have the year_of_experience column with the data type is INTERVAL YEAR TO MONTH.

Second, insert data into the candidates table:

INSERT INTO candidates ( first_name, last_name, job_title, year_of_experience ) VALUES ( 'Camila', 'Kramer', 'SCM Manager', INTERVAL '10-2' YEAR TO MONTH );Code language: SQL (Structured Query Language) (sql)

In this statement, we inserted an interval literal of 10 years 2 months into the year_of_experience column.

INSERT INTO candidates ( first_name, last_name, job_title, year_of_experience ) VALUES ('Keila', 'Doyle', 'SCM Staff', INTERVAL '9' MONTH);Code language: SQL (Structured Query Language) (sql)

In this statement, we inserted an interval literal 9 months into the year_of_experience column.

Third, query data from the INTERVAL YEAR TO MONTH column:

SELECT * FROM candidates; Code language: SQL (Structured Query Language) (sql)

Converting a number to an INTERVAL YEAR TO MONTH #

To convert a number to an INTERVAL YEAR TO MONTH, you use the numtoyminterval() function. For example:

SELECT numtoyminterval(37, 'MONTH') interval;Code language: JavaScript (javascript)

Output:

INTERVAL -------- +03-01

In this example, we use the numtoyminterval function to convert 37 months to a YEAR TO MONTH interval. The result is an interval of 3 years 1 month (+03-01).

Calculating an INTERVAL YEAR TO MONTH between two dates #

In Oracle, when you subtract two dates or timestamps, you get a number of day (and fractions of a day if time is included. Oracle does not automatically convert the result to an interval.

To convert the result of two dates into an interval, you can use the months_between , trunc, and numtoyminterval() functions:

months_between function returns the month between two dates.
trunc function removes the decimal part and returns full months.
numtoyminterval() function converts the number of months to an INTERVAL YEAR TO MONTH.

For example, the following statement returns an INTERVAL YEAR TO MONTH between 2025-05-11 and 2020-04-11:

SELECT NUMTOYMINTERVAL ( TRUNC ( MONTHS_BETWEEN (DATE '2025-05-11', DATE '2020-04-10') ), 'MONTH' ) RESULT FROM dual;Code language: JavaScript (javascript)

Output:

RESULT ------ +05-01

It returns an interval of 5 years and 1 month.

How it works:

First, the months_between function returns 61.0322581 months.
Second, the trunc function converts 61.0322581 to 61.
Third, the numtoyminterval converts 61 months to an interval of 5 year 1 month.

Oracle INTERVAL DAY TO SECOND data type #

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

The following shows the syntax of the INTERVAL DAY TO SECOND data type:

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]Code language: SQL (Structured Query Language) (sql)

In this syntax:

day_precision is the number of digits in the DAY field. It ranges from 0 to 9. By default, its value is set to 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. It ranges from 0 through 9. If you omit the fractional_seconds_precision, it defaults to 6.

Oracle INTERVAL DAY TO SECOND literals #

The literal form of INTERVAL DAY TO SECOND is as follows:

INTERVAL leading (leading_precision) TO trailing(fractional_seconds_precision)Code language: SQL (Structured Query Language) (sql)

The following table shows some examples of INTERVAL DAY TO SECOND literals:

INTERVAL DAY TO SECOND Literals	Meaning
`INTERVAL '11 10:09:08.555' DAY TO SECOND(3)`	11 days, 10 hours, 09 minutes, 08 seconds, and 555 thousandths of a second.
`INTERVAL '11 10:09' DAY TO MINUTE`	11 days, 10 hours, and 09 minutes.
`INTERVAL '100 10' DAY(3) TO HOUR`	100 days 10 hours.
`INTERVAL '999' DAY(3)`	999 days.
`INTERVAL '09:08:07.6666666' HOUR TO SECOND(7)`	9 hours, 08 minutes, and 7.6666666 seconds.
`INTERVAL '09:30' HOUR TO MINUTE`	9 hours and 30 minutes.
`INTERVAL '8' HOUR`	8 hours.
`INTERVAL '15:30' MINUTE TO SECOND`	15 minutes 30 seconds.
`INTERVAL '30' MINUTE`	30 minutes.
`INTERVAL '5' DAY`	5 days.
`INTERVAL '40' HOUR`	40 hours.
`INTERVAL '15' MINUTE`	15 minutes.
`INTERVAL '250' HOUR(3)`	250 hours.
`INTERVAL '15.6789' SECOND(2,3)`	Rounded to 15.679 seconds. Because the precision is 3, the fractional second ‘6789’ is rounded to ‘679’

Summary #

Use the Oracle interval data types to store interval.
Use the INTERVAL YEAR TO MONTH to store intervals in years and months.
Use the INTERVAL DAY TO SECOND data type to store intervals in days, hours, minutes, and seconds.

Was this tutorial helpful?