Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL INTERVAL Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 168 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?