SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—523725-004
6-32
Interval Data Types
SQL/MP Considerations for Interval Data Types
Selecting INTERVAL Columns in SQL/MP Tables
SQL/MP INTERVAL values represent durations of time in year-month units (years and
months), in day-time units (days, hours, minutes, seconds, and fractions of a second),
or in subsets of those units.
A specific INTERVAL data type consists of a subset or range of these fields and a
specified number of significant digits for the FRACTION field if it exists. For example:
INTERVAL YEAR TO MONTH
INTERVAL DAY TO FRACTION(3)
Selecting Supported INTERVAL Columns
NonStop SQL/MX supports accessing any INTERVAL column—except those
consisting of FRACTION only. For example, suppose that an SQL/MP table has an
INTERVAL column defined as:
MPIntervalCol INTERVAL SECOND(2) TO FRACTION(1)
DEFAULT INTERVAL '36.8' SECOND(2) TO FRACTION(1)
You can select from this column as shown:
SELECT MPIntervalCol FROM MPTable;
MPIntervalCol
-------------
...
36.8
...
In this example, the SQL/MP column of type INTERVAL SECOND(2) TO
FRACTION(1) is interpreted in NonStop SQL/MX as type INTERVAL SECOND(2,1).
In general, a FRACTION end field in NonStop SQL/MP is interpreted in NonStop
SQL/MX as though it were the fractional precision of a SECOND field, provided the
start field is SECOND or larger.
Selecting FRACTION-Only INTERVAL Columns
If you attempt to select data from a FRACTION-only INTERVAL column, the column
value is returned as a string of '#' characters with the same display length as the
length of the column.
For example, suppose that an SQL/MP table has an INTERVAL column defined as:
MPIntervalCol INTERVAL FRACTION(6)
DEFAULT INTERVAL '123456' FRACTION(6)
You can select from this column as shown:
SELECT MPIntervalCol FROM MPTable;
MPIntervalCol