Oracle Interval Data Type and Functions
Version 23ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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)
 
CEIL (new 23ai)
Undocumented

STANDARD package, Overload 5		 CEIL(left IN DSINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN DSINTERVAL_UNCONSTRAINED;
TBD
Undocumented

STANDARD package, Overload 6		 CEIL(left IN YMINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
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;
 
FLOOR (new 23ai)
Undocumented

STANDARD package, Overload 7		 FLOOR (left IN DSINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN DSINTERVAL_UNCONSTRAINED;
TBD
Undocumented

STANDARD package, Overload 8		 FLOOR (left IN YMINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
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
 
ROUND (new 23ai)
Rounds a DS Interval

Standard Package Overload 7		 ROUND(left IN DSINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN DSINTERVAL_UNCONSTRAINED;
TBD
Round a YM Interval

Standard Package Overload 8		 ROUND(left IN YMINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
TRUNC (new 23ai)
Rounds a DS Interval

Standard Package Overload 7		 ROUND(left IN DSINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
RETURN DSINTERVAL_UNCONSTRAINED;
TBD
Round a YM Interval

Standard Package Overload 8		 ROUND(left IN YMINTERVAL_UNCONSTRAINED, right IN VARCHAR2 DEFAULT NULL)
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'1960-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;

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

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

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;

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);
*
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);

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);

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;
*
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 21c
What's New In 26ai

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