ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
The interval data type specifically stores the interval between two date/timestamp values rather than the original values themselves
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(pattern IN DSINTERVAL_UNCONSTRAINED) RETURN DSINTERVAL_UNCONSTRAINED;
GREATEST(pattern IN YMINTERVAL_UNCONSTRAINED) RETURN YMINTERVAL_UNCONSTRAINED;
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);
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;
-- 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';
-- 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
