Term: INTERVAL

Definition:
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)

Example Syntax:

INTERVAL YEAR[PRECISION] TO MONTH;

INTERVAL DAY [PRECISION] TO SECOND[PRECISION];

Example Usage:

SELECT INTERVAL '50' MONTH RES 

FROM DUAL;



RES

----------

+004-02

SELECT INTERVAL '4' YEAR RES 

FROM DUAL;



RES

----------

+004-00

SELECT INTERVAL '3 12:30:06.7' DAY TO SECOND(1) RES 

FROM DUAL;



RES

-----------------------

+003 12:30:06.7000

Related Links:

Related Code Snippets:

Date Functions: INTERVAL - Interval to adjust date-time.
Date Functions: TO_YMINTERVAL - Converts a string to an Interval Year to Month data type.
Date Functions: TO_DSINTERVAL - Converts a string to an INTERVAL DAY TO SECOND DataType.
Date Functions: NUMTODSINTERVAL - Converts a number to an INTERVAL DAY TO SECOND literal.
Date Functions: NUMTOYMINTERVAL - Converts n to an INTERVAL YEAR TO MONTH literal.
NUMTODSINTERVAL - NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. The argument n ...
NUMTOYMINTERVAL - NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argu...
TO_DSINTERVAL - Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an ...
TO_YMINTERVAL - Converts a character string (CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to an I...