Since I’m into reusing information (long description for “lazy”) I will give you the definition of the INTERVAL datatype that comes in the manuals:
“An interval is defined as the difference between two dates and times. Intervals are expressed in one of two different ways. One is a year-month interval that expresses intervals in terms of years and an integral number of months. The other is a day-time interval that expresses intervals in terms of days, minutes, and seconds. These two types of intervals are distinct and cannot be mixed, because months can have varying numbers of days.”
So aside from beeing a record label, in SQL the datatype INTERVAL is a way of storing time intervals.
But why having two kinds? Probably because one is for storing the time coupples stay married these days, and the other for the time TV shows stay on the air (exception made for ER and SNL 
).
Since every SQL geek only gives value to a blog post if it contains SQL examples, let’s do it.
First we’ll create a table with a INTERVAL YEAR TO MONTH column, meaning the period a certain employee stayed in the company:
SQL> create table emp (empno number, hiredperiod interval year to month, empname varchar2(100)); Table created.
Then let’s try to insert a row, producing the interval range out of date subtraction:
SQL> insert into emp values (1,sysdate-(sysdate-99),'Luigi'); insert into emp values (1,sysdate-(sysdate-99),'Luigi') * ERROR at line 1: ORA-00932: inconsistent datatypes: expected INTERVAL YEAR TO MONTH got DATE JULIAN
Seems like SYSDATE is no good, let’s try the other function SYSTIMESTAMP:
SQL> insert into emp values (1,SYSTIMESTAMP-(SYSTIMESTAMP-99),'Luigi'); insert into emp values (1,SYSTIMESTAMP-(SYSTIMESTAMP-99),'Luigi') * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
What just happened here? Where is the guy that wrote the code for message ORA-00932? He’s a bit confused because a subtraction of dates it’s not DATE JULIAN, it’s plain and simple NUMBER! The number of days between the two dates. But the weirdness does not stop here.
The function SYSTIMESTAMP seems even more confused since the subtraction of timestamps is a timestamp and he says exactly the opposite: expecting NUMBER and got INTERVAL. Has he been drinking? Or the guy who wrote the ORA-00932 variations wanted to obscure even more this date-timestamp-interval mess?
Let’s sched some light here. The way you must refer to this datatype is by using the reserved word INTERVAL followed by the range you want to define and in the end the kind of interval: year to month or day to second.
Watch this:
SQL> insert into emp values (1,interval '0-3' year to month,'Luigi'); 1 row created.
What just happened? The answer is: we’ve inserted an interval of hiring for employee “Luigi” of 0 years and 3 months…
My shortest job ever!
Let’s select from it:
SQL> select * from emp; EMPNO HIREDPERIO EMPNAME ---------- ---------- ------------------------------ 1 +00-03 Luigi SQL> insert into emp values (2,interval '1-2' year to month,'Rod'); 1 row created. SQL> insert into emp values (3,interval '0-13' year to month,'Jill'); insert into emp values (3,interval '0-13' year to month,'Jill') * ERROR at line 1: ORA-01843: not a valid month SQL> --oopsie! SQL> SQL> insert into emp values (3,interval '0-12' year to month,'Jill'); insert into emp values (3,interval '0-12' year to month,'Jill') * ERROR at line 1: ORA-01843: not a valid month SQL> -- makes sense SQL> SQL> insert into emp values (3,interval '0-11' year to month,'Jill'); 1 row created.
The third time is the right one!
Now let’s check who’s been hired for more than 1 year. Simple query:
SQL> select * from emp 2 where hiredperiod > interval '1-0' year to month; EMPNO HIREDPERIO EMPNAME ---------- ---------- ------------------------------ 2 +01-02 Rod
If one year and two months is our record, the boss must be real nasty!
The same principles of INTERVAL YEAR TO MONTH apply to INTERVAL DAY TO SECOND.
Hope it helped to clear some minds out.
LMC.
