Oracle Interval Data Type
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose The interval data type specifically stores the interval between two date/timestamp values rather than the original values themselves
 
Syntax Examples
Interval Year To Month INTERVAL '<integer>' [YEAR | MONTH]
INTERVAL '<integer>' [YEAR | MONTH] (<precision>)
INTERVAL '<integer>' [YEAR | MONTH] (<precision>) TO [YEAR | MONTH]
-- 2 years, 0 months
INTERVAL '2' YEAR

-- interval of 100 years 4 months
INTERVAL '100-4' YEAR(3) TO MONTH
-- precision must be specified if the leading field is greater than 2 digits

-- 42 months
INTERVAL '42' MONTH
An interval of 300 months
Interval Day To Second INTERVAL '<integer>' [YEAR | MONTH]
INTERVAL '<integer><time_expression>' [YEAR | MONTH]
INTERVAL '<time_expression>' [YEAR | MONTH]
INTERVAL '<integer>' [DAY | HOUR | MINUTE] (<precision>) TO [DAY | HOUR | MINUTE | SECOND]
INTERVAL '<integer>' [SECOND] (<leading_precision>) TO [DAY | HOUR | MINUTE | SECOND]
-- second also contains an optional fractional second precision option
-- 42 minutes
INTERVAL '42' MINUTE

-- 23 hours
INTERVAL '23' HOUR

-- 2 days
INTERVAL '2' DAY

-- 180 days
INTERVAL '180' DAY(3)

-- 10 minutes 42 seconds
INTERVAL '10:22' MINUTE TO SECOND

-- 11 hours, 12 minutes, and 10.2222222 seconds
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)

-- 9 hours and 11 minutes
INTERVAL '9:11' HOUR TO MINUTE

-- 4 days, 2 hours and 42 minutes
INTERVAL '4 2:42' DAY TO MINUTE

-- 363 days 12 hours
INTERVAL '365 12' DAY(3) TO HOUR

-- 30.1235 seconds. Seconds of '12345' are rounded to '1235' since the precision is 4
INTERVAL '30.12345' SECOND(2,4)

-- 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
 
DUMP
Returns the number of bytes and datatype of a value DUMP(<value>)
set linesize 141
col duration_1 format a22
col duration_2 format a10
col dump1 format a50
col dump2 format a50

desc tint_test

-- the table tint_test is built below in the demo section
SELECT duration_1, dump(duration_1) DUMP1, duration_2, dump(duration_2) DUMP2
FROM tint_test;
 
GREATEST
Return the Largest Interval GREATEST(pattern IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED;
GREATEST(pattern IN YMINTERVAL_UNCONSTRAINED) RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
LEAST
Returns the Smallest Interval LEAST(pattern IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED;
LEAST(pattern IN YMINTERVAL_UNCONSTRAINED) RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
Demos
Interval Demo conn uwclass/uwclass@pdbdev

CREATE TABLE tint_test (
msg        VARCHAR2(25),
start_date TIMESTAMP WITH TIME ZONE,
end_date   TIMESTAMP WITH TIME ZONE,
duration_1 INTERVAL DAY(5) TO SECOND,
duration_2 INTERVAL YEAR TO MONTH);

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my plane ride',
timestamp'2004-08-08 17:02:32.212 US/Eastern',
timestamp'2004-08-08 19:10:12.235 US/Pacific');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my vacation', timestamp'2004-07-27 06:00:00', timestamp'2004-08-04 18:00:00');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my life', timestamp'1950-01-15 02:00:00', current_timestamp);

SELECT * FROM tint_test;

UPDATE tint_test
SET duration_1 = (end_date - start_date) DAY(5) TO SECOND,
    duration_2 = (end_date - start_date) YEAR TO MONTH;

SELECT msg, duration_1, duration_2 FROM tint_test;

SELECT t.*, end_date - start_date FROM tint_test t;
Interval Math Demo conn uwclass/uwclass@pdbdev

SELECT current_timestamp
FROM dual;

SELECT current_timestamp + INTERVAL '5' year(1)
FROM dual;

SELECT current_timestamp + INTERVAL '10:30' MINUTE TO SECOND
FROM dual;

-- this will fail ... there is no Feb. 29th in 2005
SELECT timestamp'2004-02-29 00:00:00' + INTERVAL '1' year(1)
FROM dual;

SELECT timestamp'2004-02-28 00:00:00' + INTERVAL '1' year(1)
FROM dual;

SELECT add_months(timestamp'2004-02-29 00:00:00',12)
FROM dual;

-- math with intervals
SELECT a.duration_1 + b.duration_1 + c.duration_1
FROM tint_test a, tint_test b ,tint_test c
WHERE a.msg = 'my plane ride'
AND b.msg LIKE '%vacat%'
AND c.msg like '%life';

-- but not aggregations
SELECT SUM(duration_1)
FROM tint_test;

Related Topics
Analytic Functions
Built-in Functions
Conversion Functions
Data Types
Data Mining Functions
Date Functions
Miscellaneous Functions
Numeric Functions
String Functions
TimeStamp Data Type & Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved