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