Interval

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

Example

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

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

Example

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

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	#