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: DATEand 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.
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:
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:
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:
| 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 '500' MONTH(3) | An interval of 500 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, let’s create a new table named candidates for the demonstration:
In this table, we have the year_of_experience column whose the data type is INTERVAL YEAR TO MONTH.
Second, insert data into the candidates table:
In this statement, we inserted an interval literal of 10 years 2 months into the year_of_experience column.
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:
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:
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:
The following table shows some examples of INTERVAL YEAR TO SECOND literals:
| INTERVAL YEAR 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’ |
In this tutorial, you have learned how to use the Oracle INTERVAL data type to store periods of time in the tables.