Oracle Interval Datatype

by

In this Oracle tutorial, we will learn about the Interval datatype in the oracle database. Also, we will illustrate how to use it to create Interval columns for a table in oracle.

Introduction to Oracle Interval Datatype

A unique data type called INTERVAL is utilized in Oracle Database 21c to hold durational or time-based intervals. It is particularly useful for calculating time intervals or durations between two points in time, such as the time between two events or the duration of a task.

  • There are two types of interval
    • Interval Year to month: It Contains intervals using year and month.
    • Interval Day to second: Using days, hours, minutes, and seconds, including fractional seconds, intervals are stored.

Also, check: Oracle Binary_double Datatype

Oracle interval Datatype Syntax

The syntax for defining an INTERVAL data type is as follows:

INTERVAL [(precision)] {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

Here, precision specifies the number of digits to the right of the decimal point for the seconds component. The possible interval units are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

Oracle INTERVAL YEAR TO MONTH

An Oracle data type called INTERVAL YEAR TO MONTH is used to describe a period of time in years and months. A time interval between two points in space, expressed in years and months, is stored using this data type. The format YEAR-MONTH, with an optional sign, is used to provide the YEAR and MONTH components.

Syntax of INTERVAL YEAR TO MONTH

INTERVAL YEAR [(year_precision)] TO MONTH

The number of digits in the YEAR field is represented by the year precision. It has a range of 0 to 9.

The year precision is not necessary. When the year precision option is absent, it defaults to 2. In other words, you are only permitted to keep data for up to 99 years and 11 months by default, and this duration must be shorter than 100 years.

Read: How to add a column to a table in Oracle

Example of INTERVAL YEAR TO MONTH

First, let’s create a new table named employees for the demonstration.

CREATE TABLE employees (
    emp_id NUMBER,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    Designation VARCHAR2(255) NOT NULL,
    year_of_experience INTERVAL YEAR TO MONTH,
    PRIMARY KEY (emp_id)
);

In the following given table, we have the year_of_experience column whose data type is INTERVAL YEAR TO MONTH.

INSERT INTO employees (
    emp_id,
    first_name,
    last_name,
    designation,
    year_of_experience
    )
VALUES (
     1762,
    'John',
    'Peter',
    'Developer',
    INTERVAL '9-2' YEAR TO MONTH
    );
    
select * from employees;

We added an interval literal of 10 years and 2 months to the year of experience column in the given statement.

Using the interval year to month datatype in oracle 21c
Using the interval year-to-month datatype in oracle 21c

Oracle INTERVAL DAY TO SECOND data type

The INTERVAL DAY TO SECOND data type in Oracle 21c is used to represent a duration of time between two points in time that is measured in days, hours, minutes, and seconds, including fractional seconds.

It is useful for storing time durations, such as the length of a movie, the duration of a phone call, or the length of time between two events.

Syntax of Oracle INTERVAL DAY TO SECOND data type

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

In the following given syntax:

The precision parameter, which must be between 1 and 9, determines the maximum number of digits that can be used to represent the DAY component.

The fractional seconds parameter, which must be between 0 and 6, determines the maximum number of decimal places that can be used to represent the SECOND component.

Example:

Here is an example of how to create an INTERVAL DAY TO SECOND data type:

CREATE TABLE movie (
   duration INTERVAL DAY TO SECOND(3)
);


INSERT INTO movie (duration) VALUES (INTERVAL '4 12:30:00.123' DAY TO SECOND);

SELECT duration FROM movie;

This will create a table with a column called the duration of data type INTERVAL DAY TO SECOND with a precision of 3 fractional seconds. It will then insert a row with a value of 4 days, 12 hours, 30 minutes, 0 seconds, and 123 milliseconds.

using the interval day to seconds in oracle 21c
using the interval day to seconds in oracle 21c

Advantages of Oracle INTERVAL DAY TO SECOND data type

  • Time durations measured in days, hours, minutes, and seconds, including fractional seconds, can be accurately stored and manipulated using the INTERVAL DAY TO SECOND data type.
  • Easy arithmetic operations: The INTERVAL DAY TO SECOND data type allows for easy arithmetic calculations with time durations. You can add, subtract, multiply, and divide INTERVAL DAY TO SECOND values, making it easy to perform time-based operations such as calculating the length of time between two events.
  • Increased data integrity: Using the INTERVAL DAY TO SECOND data type makes sure that only genuine time durations are kept in the database, which improves data integrity. As a result, data input errors are reduced and correct and consistent data is produced.

Also, check: Date Datatype in Oracle Database

Conclusion:

So, in this Oracle tutorial, we understood how to define and use the interval Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.