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
There are two types of
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.
Oracle INTERVAL YEAR TO MONTH
INTERVAL YEAR TO MONTH data type allows you to store a period of time using the
The following illustrates an
INTERVAL YEAR TO MONTH:
INTERVAL YEAR [(year_precision)] TO MONTH
year_precision represents the number of digits in the
YEAR field. It ranges from 0 to 9.
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 trailing
The following are arguments:
month are integers for the leading and trailing fields of the interval. If
MONTH, then the
month field ranges from 0 to 11.
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
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:
|INTERVAL YEAR TO MONTH Literals||Meaning|
|An interval of 120 years, 3 months; Must specify the leading field precision |
|An interval of 105 years 0 months.|
|An interval of 500 months.|
|9 years, which is equivalent to |
|40 months or 3 years 4 months, which is equivalent to |
|Invalid interval because ‘180’ has 3 digits which are greater than the default precision (2)|
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) );
In this table, we have the
year_of_experience column whose the data type is
INTERVAL YEAR TO MONTH.
Second, insert data into the
INSERT INTO candidates ( first_name, last_name, job_title, year_of_experience ) VALUES ( 'Camila', 'Kramer', 'SCM Manager', INTERVAL '10-2' YEAR TO MONTH );
In this statement, we inserted an interval literal of 10 years 2 months into the
INSERT INTO candidates ( first_name, last_name, job_title, year_of_experience ) VALUES ( 'Keila', 'Doyle', 'SCM Staff', INTERVAL '9' MONTH );
In this statement, we inserted an interval literal 10 months into the
Third, query data from the
INTERVAL YEAR TO MONTH column:
SELECT * FROM candidates;
Oracle INTERVAL DAY TO SECOND data type
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.
Oracle INTERVAL YEAR TO SECOND literals
The literal form of
INTERVAL YEAR TO SECOND is as follows:
INTERVAL leading (leading_precision) to trailing(fractional_seconds_precision)
The following table shows some examples of
INTERVAL YEAR TO SECOND literals:
|INTERVAL YEAR TO SECOND Literals||Meaning|
|11 days, 10 hours, 09 minutes, 08 seconds, and 555 thousandths of a second.|
|11 days, 10 hours, and 09 minutes.|
|100 days 10 hours.|
|9 hours, 08 minutes, and 7.6666666 seconds.|
|9 hours and 30 minutes.|
|15 minutes 30 seconds.|
|Rounded to 15.679 seconds. Because the precision is 3, the fractional second ‘6789’ is rounded to ‘679’|
In this tutorial, you have learned how to use the Oracle INTERVAL data type to store periods of time in the tables.