Setting Interval Unit Display
You can control how Vertica displays interval units in a SELECT INTERVAL
query, with SET INTERVALSTYLE
and SET DATESTYLE
. These statements only affect interval output format.
Important: DATESTYLE
settings supersede INTERVALSTYLE
. If DATESTYLE
is set to SQL, interval unit display always conforms to the SQL:2008 standard, which omits interval unit display. If DATESTYLE
is set to ISO, you can use SET INTERVALSTYLE
to omit or display interval unit display, as described below.
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
, Vertica 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