Interval

From Oracle FAQ
Jump to: navigation, search

INTERVAL datatype is a data type used to store a period of time. There are 2 kinds of interval: INTERVAL YEAR TO MONTH, to store a difference in years and months, and INTERVAL DAY TO SECOND, to store a difference in days, hours, minutes and seconds.

INTERVAL YEAR TO MONTH[edit]

Example[edit]

SQL> CREATE TABLE t1 (c1 INTERVAL YEAR(3) TO MONTH);

Table created.

SQL> INSERT INTO t1 VALUES (TO_YMINTERVAL('01-02'));

1 row created.

SQL> SELECT * FROM t1;
C1
----------------------------------------------------
+001-02

The parameter of YEAR in the type definition gives the number of decimals for the year part. It could be from 0 to 9, 2 is the default value.

The TO_YMINTERVAL function converts a string representing a number of years and a number of months into an INTERVAL YEAR TO MONTH datatype.

Internal format[edit]

The INTERVAL YEAR TO MONTH type takes 5 bytes.

SQL> SELECT dump(c1) FROM t1;
DUMP(C1)
---------------------------
Typ=182 Len=5: 128,0,0,1,62

The first 4 bytes gives the number of years, the fifth byte gives the number of months in the following format:

  • years + 2147483648
  • months + 60

INTERVAL DAY TO SECOND[edit]

Example[edit]

SQL> CREATE TABLE t1 (c1 INTERVAL DAY(5) TO SECOND(3));

Table created.

SQL> INSERT INTO t1 VALUES (TO_DSINTERVAL('2 10:20:30.456'));

1 row created.
SQL> SELECT * FROM t1;
C1
----------------------
+00002 10:20:30.456

The parameter of DAY in the type definition gives the number of decimals for the day part. It could be from 0 to 9, 2 is the default value. The parameter of SECOND gives the number of decimals for the fractional part of the seconds. It could be from 0 to 9, 6 is the default value.

The TO_DSINTERVAL function converts a string representing a number of days, hours, minutes and seconds into an INTERVAL DAY TO SECOND datatype.

Internal format[edit]

The INTERVAL DAY TO SECOND type takes 11 bytes.

SQL> SELECT dump(c1) FROM t1;
DUMP(C1)
---------------------------------------------
Typ=183 Len=11: 128,0,0,2,70,80,90,155,46,2,0

The first 4 bytes gives the number of days, the last 4 ones the number of nanoseconds and the 3 in the middle the number of hours, minutes and seconds in the following format:

  • days + 2147483648
  • hours + 60
  • minutes + 60
  • seconds + 60
  • nanoseconds
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #