Oracle INTERVAL

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

Introduction to Oracle data type

Oracle provides you with two date-time data types: DATE and TIMESTAMP for storing point-in-time data. In addition, It provides the INTERVAL data type that allows you to store periods of time.

There are two types of INTERVAL:

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

Oracle INTERVAL YEAR TO MONTH

The INTERVAL YEAR TO MONTH data type allows you to store a period of time using the YEAR and MONTH fields.

The following illustrates an INTERVAL YEAR TO MONTH:

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.

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

Oracle INTERVAL YEAR TO MONTH literals

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

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

Where leadingand trailingcan be YEAR or MONTH.

The following are arguments:

'year[-month]'

The year and month are integers for the leading and trailing fields of the interval. If leading is  YEAR and trailing is MONTH, then the month field ranges from 0 to 11.

The trailing field must be less than the leading field. For example, INTERVAL '1-2' MONTH TO YEAR is invalid because trailing is YEAR which is greater than the leading field which is MONTH.

precision

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

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

Oracle INTERVAL YEAR TO MONTH example

First, let’s create a new table named candidates for the demonstration:

CREATE TABLE candidates (
    candidate_id NUMBER,
    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,
    PRIMARY KEY (candidate_id)
);
Code language: SQL (Structured Query Language) (sql)

In this table, we have the year_of_experience column whose 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 10 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)

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:

In this tutorial, you have learned how to use the Oracle INTERVAL data type to store periods of time in the tables.