INTERVAL: Clock Math
INTERVAL is a function that provides a way to add, subtract or just convert.
SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, INTERVAL '11:12:10.1234567' HOUR FROM DUAL;
Which is the correct output of the above query?
A. +25-00, +54-02, +00 11:12:10.123457
B. +00-300, +54-02,+00 11:12:10.123457
C. +25-00,+00-650,+00 11:12:10.123457
D. +00-300,+00-650,+00 11:12:10.123457
ANSWER: A
INTERVAL ‘x’ [TIMEFRAME]
The interval command returns an interval of time as an expression. These intervals can be stored in two formats:
- YEAR TO MONTH yy-mm
- DAY TO SECOND dd hh:mi:ss
From the sounds of it, it’s converting years to months and days to seconds, but it isn’t. It is telling you the range of values found in the interval. It goes all the way from YEAR down to MONTH and all the way from DAY down to SECOND.
For example, MONTH will return the years-months for a given number of months. It can also be used to add or subtract an interval of months to a date.
** NOTE: It will only add or subtract by the number of months. The day will remain the same.
When people talk about their babies, they refer to the child’s age in months. “He’s 16 months old tolday!” I don’t know what you think when you hear that, but my mind says, “okay, so one year and four months old.”
OR we can ask Oracle!
SQL> select interval '16' month from dual; INTERVAL'16'MONTH -------------------------------------------------- +01-04 1 row selected.
It gives us the same answer, but in slightly strange notation using yy-mm. The first number (01) is the number of years. The second number (04) is the number of months. The plus indicator preceding the year shows the interval is positive.
We could run the same again with months in the past:
SQL> select interval '-16' month from dual; INTERVAL'16'MONTH -------------------------------------------------- -01-04 1 row selected.
We can substract that interval (or add the negative interval) to today’s date and find out when the child was born:
SQL> select to_date('05-JUN-2015') + interval '-16' month from dual;
TO_DATE('05-JUN-2015')+INTERVAL'-16'MONTH
-----------------------------------------
05-FEB-14
1 row selected.
When will he be 18 months old?
SQL> select to_date('05-FEB-14') + interval '+18' month from dual;
TO_DATE('05-FEB-14')+INTERVAL'+18'MONTH
---------------------------------------
05-AUG-15
1 row selected.
By default, the month interval has a precision of 4 which would be anything up to 99 years and 11 months. You can change that by adding your own precision. How many years and months is 1200 months?
Notice in the example below, the first and second queries are the same except for the (4) interval on the second.
SQL> select interval '1199' month from dual; INTERVAL'1199'MONTH -------------------------------------------------- +99-11 1 row selected. SQL> select interval '1199' month(4) from dual; INTERVAL'1199'MONTH(2) -------------------------------------------------- +99-11 1 row selected. SQL> select interval '1200' month(4) from dual; select interval '1200' month(4) from dual * Error at line 1 ORA-01873: the leading precision of the interval is too small SQL> select interval '1200' month(5) from dual; INTERVAL'1200'MONTH(5) -------------------------------------------------- +100-00 1 row selected.
Other Intervals
Each time frame can be used with the interval command.
SQL> select interval '1' day from dual; INTERVAL'1'DAY --------------------------------------------- +01 00:00:00 SQL> select interval '1' hour from dual; INTERVAL'1'HOUR --------------------------------------------- +00 01:00:00 SQL> select interval '1' minute from dual; INTERVAL'1'MINUTE --------------------------------------------- +00 00:01:00 SQL> select interval '1' second from dual; INTERVAL'1'SECOND --------------------------------------------- +00 00:00:01.000000 SQL> select interval '1' year from dual; INTERVAL'1'YEAR --------------------------------------------- +01-00 SQL> select interval '1' month from dual; INTERVAL'1'MONTH --------------------------------------------- +00-01 SQL> select interval '1' day from dual; INTERVAL'1'DAY --------------------------------------------- +01 00:00:00 SQL> select interval '1' hour from dual; INTERVAL'1'HOUR --------------------------------------------- +00 01:00:00 SQL> select interval '1' minute from dual; INTERVAL'1'MINUTE --------------------------------------------- +00 00:01:00 SQL> select interval '1' second from dual; INTERVAL'1'SECOND --------------------------------------------- +00 00:00:01.000000
Interval Math
Now that we have intervals, we can use them to add to or subtract from other intervals or dates.
SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') right_now, 2 to_char(sysdate - interval '15' minute, 'DD-MON-YYYY HH24:MI:SS') fifteen_mins_ago 3 from dual 4 / RIGHT_NOW FIFTEEN_MINS_AGO ----------------------------- ----------------------------- 06-JUN-2015 18:29:31 06-JUN-2015 18:14:31
We can also use intervals in procedures. Here’s an anonymous block that reels back time 15 minutes and then 30 months. Don’t forget to turn serveroutput on so you can see the results. See the four stars to note where the interval is used with the date.
SQL> set serveroutput on
SQL> declare
2 v_mins interval day to second;
3 v_months interval year to month;
4 v_new_date date;
5 begin
6 v_mins := interval '15' minute; -- positive 15 minutes
7 dbms_output.put_line ('v_15mins INTERVAL: ' || v_mins);
8 v_new_date := sysdate-v_mins; --subtracting a positive interval ****
9 dbms_output.put_line ('NOW: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
10 dbms_output.put_line ('15 MINS AGO: ' || to_char(v_new_date,'DD-MON-YYYY HH24:MI:SS'));
11
12 v_months := interval '-30' month; --negative 30 months ****
13 dbms_output.put_line('v_months INTERVAL: ' || v_months);
14 v_new_date := sysdate+v_months; --adding a negative interval
15 dbms_output.put_line ('NOW: ' || to_char(sysdate,'DD-MON-YYYY'));
16 dbms_output.put_line ('30 MONTHS AGO: ' || to_char(v_new_date, 'DD-MON-YYYY'));
17 end;
18 /
v_15mins INTERVAL: +00 00:15:00.000000
NOW: 06-JUN-2015 18:43:43
15 MINS AGO: 06-JUN-2015 18:28:43
v_months INTERVAL: -02-06
NOW: 06-JUN-2015
30 MONTHS AGO: 06-DEC-2012
PL/SQL procedure successfully completed.
One thought on “INTERVAL: Clock Math”