Oracle Interval Data Type Version 12.1.0.1 

General Information  
Library Note 


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 '1004' 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'20040808 17:02:32.212 US/Eastern', timestamp'20040808 19:10:12.235 US/Pacific'); INSERT INTO tint_test (msg, start_date, end_date) VALUES ('my vacation', timestamp'20040727 06:00:00', timestamp'20040804 18:00:00'); INSERT INTO tint_test (msg, start_date, end_date) VALUES ('my life', timestamp'19500115 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'20040229 00:00:00' + INTERVAL '1' year(1) FROM dual; SELECT timestamp'20040228 00:00:00' + INTERVAL '1' year(1) FROM dual; SELECT add_months(timestamp'20040229 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; 
This site is maintained by Dan Morgan. Last Updated:  This site is protected by copyright and trademark laws under U.S. and International law. © 19982014 Daniel A. Morgan All Rights Reserved  

