NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
I-21
Considerations—INTERVAL Data Type
Considerations—INTERVAL Data Type
•
Compatibility with other data types
A specific INTERVAL data type is compatible only with another INTERVAL data
type that has the same range of INTERVAL fields.
•
Range of INTERVAL values
An INTERVAL value can have a maximum of 18 digits, including the digits in all
fields.
Any INTERVAL field that is a starting field can have up to 18 digits minus the
number of other digits in the INTERVAL value (but the starting field will have only
2 digits unless you specify a larger value with the digits option). The maximum
value for the starting field is the maximum value that can be expressed in the
number of digits allowed for the field.
If an INTERVAL field is not a starting field, the maximum number of digits in the
field is as follows:
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.
•
Size of an INTERVAL column
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.
YEAR (Always a starting field)
MONTH 0 to 11
DAY (Always a starting field)
HOUR 0 to 23
MINUTE 0 to 59
SECOND 0 to 59
FRACTION 0 to 999999 (less with small precisions)