| SQL adaptation guide for Oracle Database 11, 12 / Data dictionary | |
Informix's INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes: year-month intervals and day-time intervals.
ORACLE supports the INTERVAL data type similar to Informix®, with two classes (YEAR TO MONTH and DAY TO SECOND), but Oracle's INTERVAL cannot be defined with a precision different from these two classes (for example, you cannot define an INTERVAL HOUR TO MINUTE in Oracle). The class DAY TO SECOND(n) is equivalent to the Informix INTERVAL class DAY TO FRACTION(n).
Informix INTERVAL YEAR(n) TO MONTH data is stored in Oracle INTERVAL YEAR(n) TO MONTH columns. These data types are equivalent.
Informix INTERVAL DAY(n) TO FRACTION(p) data is stored in Oracle INTERVAL DAY(n) TO SECOND(p) columns. These data types are equivalent.
Other Informix INTERVAL types must be stored in CHAR() columns as with Oracle 8i, because the high qualifier precision cannot be specified with Oracle INTERVALs. For example, Informix INTERVAL HOUR(5) TO MINUTE has no native equivalent in Oracle.