SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
I-21
Example—INTERVAL Data Type
An INTERVAL can be negative, but individual fields within the interval are
expressed as positive values. The negative sign (-), if present, applies to the entire
value, and is not counted in the number of digits for any field.

To compute the size of an INTERVAL column:

Add 1 byte for the sign.

For the starting field, add 2 bytes for 1 to 4 digits, 4 bytes for 5 to 8 digits, and
8 bytes for 9 to 18 digits.

Add 2 bytes for each nonstarting field other than FRACTION.

If a FRACTION field is present and is not a starting field, add 2 bytes for a
precision of 1 to 4 significant digits and 4 bytes for a precision of 5 or 6 digits.

If the column allows null values, add 2 bytes.
You can also determine the storage size for a column by querying the
COLSIZE column of the COLUMNS catalog table. For example, this query
from SQLCI returns a column's length in bytes:
>>SELECT colsize FROM columns
+> WHERE tablename LIKE "%table-name%"
+> AND colname = "column-name";
* Version Management Consideration
The INTERVAL data type is supported on NonStop SQL/MP versions 2 and
later.
Example—INTERVAL Data Type
This statement creates a table in which three of the four columns are of data type
INTERVAL. Column AGE represents an interval of years and months (for example 27-
2, which means 27 years and 2 months), column YRS_EXPERIENCE represents an
interval of years, and column HOURS_VACATION represents an interval of hours.
CREATE TABLE EMPLOYEE (
AGE INTERVAL YEAR TO MONTH,
NAME PIC X(30) NO DEFAULT NOT NULL,
YRS_EXPERIENCE INTERVAL YEAR,
HOURS_VACATION INTERVAL HOUR(3) NOT NULL
)
YRS_EXPERIENCE can be no more than 99 (the default is two digits), but
HOURS_VACATION can be up to 999 because the CREATE TABLE statement
explicitly specifies three digits.
MINUTE 1 to 59
SECOND 1 to 59
FRACTION 1 to 999999 (less with small precision)