Oracle Interval Data Type
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. 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 and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
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 data type 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'2016-12-08 17:02:32.212 US/Eastern',
timestamp'2016-12-08 19:10:12.235 US/Pacific');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my vacation', timestamp'2016-12-27 06:00:00', timestamp'2016-12-29 18:00:00');

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

set linesize 161
col start_date format a40
col end_date format a40
col duration_1 format a25
col duration_2 format a11

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 2015
SELECT timestamp'2015-02-29 00:00:00' + INTERVAL '1' year(1)
FROM dual;

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

SELECT add_months(timestamp'2016-12-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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved