SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-29
Interval Data Types
{YEAR | MONTH | DAY | HOUR | MINUTE} [(leading-precision)]
specifies the start-field. A start-field can have a leading-precision
up to 18 digits (the maximum depends on the number of fields in the interval). The
leading-precision is the number of digits allowed in the start-field. The
default for leading-precision is 2.
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND [(fractional-
precision)]
specifies the end-field. If the end-field is SECOND, it can have a
fractional-precision up to 6 digits. The fractional-precision is the
number of digits of precision after the decimal point. The default for
fractional-precision is 6.
start-field | SECOND [(leading-precision,
fractional-precision)]
specifies the single-field. If the single-field is SECOND, the
leading-precision is the number of digits of precision before the decimal
point, and the fractional-precision is the number of digits of precision after
the decimal point.
The default for leading-precision is 2, and the default for
fractional-precision is 6. The maximum for leading-precision is 18,
and the maximum for fractional-precision is 6.
Considerations for Interval Data Types
Interval Leading Precision
The maximum for the leading-precision depends on the number of fields in the
interval and on the fractional-precision. The maximum is computed as:
max-leading-precision = 18 - fractional-precision - 2 * (N - 1)
where N is the number of fields in the interval.
For example, the maximum number of digits for the leading-precision in a column
with data type INTERVAL YEAR TO MONTH is computed as: 18 – 0 – 2 * (2 – 1) = 16
Interval Ranges
Within the definition of an interval range (other than a single field), the start-field
and end-field can be any of the specified fields with these restrictions:
•
An interval range is either year-month or day-time—that is, if the start-field is
YEAR, the end-field is MONTH; if the start-field is DAY, HOUR, or
MINUTE, the end-field is also a time field.
•
The start-field must precede the end-field within the hierarchy: YEAR,
MONTH, DAY, HOUR, MINUTE, and SECOND.
Signed Intervals