Oracle Interval Data Type
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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'2020-12-08 17:02:32.212 US/Eastern',
timestamp'2020-12-08 19:10:12.235 US/Pacific');

INSERT INTO tint_test
(msg, start_date, end_date)
VALUES
('my vacation', timestamp'2020-12-17 06:00:00', timestamp'2020-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;

CURRENT_TIMESTAMP
------------------------------------
22-DEC-20 11.40.45.508000 AM -06:00


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

CURRENT_TIMESTAMP+INTERVAL'5'YEAR(1)
---------------------------------------
22-DEC-25 11.40.51.160000000 AM -06:00


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

CURRENT_TIMESTAMP+INTERVAL'10:30'MINUTETOSECOND
------------------------------------------------
22-DEC-20 11.53.20.928000000 AM -06:00


-- this will fail ... there is no Feb. 29th in 2019
SELECT timestamp'2019-02-29 00:00:00' + INTERVAL '1' year(1)
FROM dual;
SELECT timestamp'2019-02-29 00:00:00' + INTERVAL '1' year(1)
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month


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

TIMESTAMP'2020-12-2800:00:00'+INTERVAL'1'YEAR(1)
------------------------------------------------
28-DEC-21 12.00.00.000000000 AM


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

ADD_MONTHS(TIMESTAMP
--------------------
29-DEC-2021 00:00:00


-- math works 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';

A.DURATION_1+B.DURATION_1+C.DURATION_1
---------------------------------------
+000025922 02:45:50.295000000


-- but not aggregations do not
SELECT SUM(duration_1)
FROM tint_test;
SELECT SUM(duration_1)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

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 19c
What's New In 20c-21c

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