In Oracle PL/SQL, the term INTERVAL refers to a period of time. It can be bifurcated or split in datetime fields in terms of 'years and months', or in terms of 'days, hours, minutes and seconds'. The two interval data types available in Oracle are YEAR_TO_MONTH and DAY_TO_SECOND.
YEAR_TO_MONTH stores the time period as an interval of years to the nearest month, while DAY_TO_SECOND stores it as interval of days to the nearest second.
YEAR_TO_MONTH interval literals use a hyphen (-) between the YEAR and MONTH. DAY_TO_SECOND interval literals use a space between the number of days and the time. The following example sets a time interval of five years and three months:
INTERVAL '5-3' YEAR TO MONTH
Note that INTERVALs can be either positive or negative, and they can be added or subtracted from the various TIMESTAMP datatypes. After addition or subraction they return the result as a TIMESTAMP.
The following syntax example uses EXTRACT to split out a specific part of an INTERVAL:
EXTRACT(time_part FROM interval_expr)
INTERVAL YEAR[PRECISION] TO MONTH;
INTERVAL DAY [PRECISION] TO SECOND[PRECISION];
SELECT INTERVAL '50' MONTH RES
SELECT INTERVAL '4' YEAR RES
SELECT INTERVAL '3 12:30:06.7' DAY TO SECOND(1) RES
Related Code Snippets: