Setting interval unit display

SET INTERVALSTYLE and SET DATESTYLE control the output format of interval units.

Omitting interval units

To omit interval units from the output, set INTERVALSTYLE to PLAIN. This is the default setting, which conforms with the SQL:2008 standard:

=> SET INTERVALSTYLE TO PLAIN;
SET
=> SELECT INTERVAL '3 2';
?column?
----------
 3 02:00

When INTERVALSTYLE is set to PLAIN, units are omitted from the output, even if the query specifies input units:

=> SELECT INTERVAL '3 days 2 hours';
 ?column?
----------
 3 02:00

If DATESTYLE is set to SQL, OpenText™ Analytics Database conforms with SQL:2008 standard and always omits interval units from output:

=> SET DATESTYLE TO SQL;
SET
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT INTERVAL '3 2';
 ?column?
----------
 3 02:00

Displaying interval units

To enable display of interval units, DATESTYLE must be set to ISO. You can then display interval units by setting INTERVALSTYLE to UNITS:

=> SET DATESTYLE TO ISO;
SET
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT INTERVAL '3 2';
 ?column?
----------------
 3 days 2 hours

Checking INTERVALSTYLE and DATESTYLE settings

Use SHOW statements to check INTERVALSTYLE and DATESTYLE settings:

=> SHOW INTERVALSTYLE;
     name      | setting
---------------+---------
 intervalstyle | units
=> SHOW DATESTYLE;
   name    | setting
-----------+----------
 datestyle | ISO, MDY