Oracle Interval Data Types
Version 11.2.0.3
 
General Information
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)
Interval Demo 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;
 
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

SELECT duration_1, dump(duration_1) DUMP1, duration_2, dump(duration_2) DUMP2
FROM tint_test;
 
GREATEST
Return the Latest Interval GREATEST(pattern IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED;
GREATEST(pattern IN YMINTERVAL_UNCONSTRAINED) RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
LEAST
Return the Earliest Interval LEAST(pattern IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED;
LEAST(pattern IN YMINTERVAL_UNCONSTRAINED) RETURN YMINTERVAL_UNCONSTRAINED;
TBD
 
Interval Demo
Interval Math Demo 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;
 
 
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