- Introduction
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 of 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 allows you to store a period of time using the YEAR and MONTH fields.
INTERVAL YEAR [(year_precision)] TO MONTH
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.
To specify literal values for the INTERVAL YEAR TO MONTH data type, you use the following format:
INTERVAL ‘year[-month]’ leading (precision) TO trailing
Let us see this with example:
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)
);
In this table, we have the year_of_experience column whose the data type is INTERVAL YEAR TO MONTH.
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 );
INSERT INTO candidates ( first_name, last_name, job_title, year_of_experience ) VALUES ( ‘Keila’, ‘Doyle’, ‘SCM Staff’, INTERVAL ‘9’ MONTH );
- INTERVAL YEAR TO SECOND
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)]
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.
The literal form of INTERVAL DAY TO SECOND is as follows:
INTERVAL leading(leading_precision)to trailing(fractional_seconds_precision)
Example:
DECLARE
lag_time INTERVAL DAY(3) TO SECOND(3);
BEGIN
lag_time := ‘7 09:24:30’;
IF lag_time > INTERVAL ‘6’ DAY THEN
DBMS_OUTPUT.PUT_LINE (‘Greater than 6 days’);
ELSE
DBMS_OUTPUT.PUT_LINE (‘Less than 6 days’);
END IF;
END;
- Conclusion
From Oracle Database 23c onward, the interval data type specifically stores the interval between two date/timestamp values rather than the original values themselves.
