Specifying Interval Precision

In general, interval precision only applies to seconds. If no precision is explicitly specified, Vertica rounds precision to a maximum of six decimal places. For example:

=> SELECT INTERVAL '2 hours 4 minutes 3.709384766 seconds' DAY TO SECOND;
    ?column?
-----------------
 02:04:03.709385
(1 row)

Vertica lets you specify interval precision in two ways:

• After the INTERVAL keyword
• After the SECOND unit of an interval qualifier, one of the following:
  • DAY TO SECOND
  • HOUR TO SECOND
  • MINUTE TO SECOND
  • SECOND

For example, the following statements use both methods to set precision, and return identical results:

=> SELECT INTERVAL(4) '2 hours 4 minutes 3.709384766 seconds' DAY TO SECOND;
   ?column?
---------------
 02:04:03.7094
(1 row)

=> SELECT INTERVAL '2 hours 4 minutes 3.709384766 seconds' DAY TO SECOND(4);
   ?column?
---------------
 02:04:03.7094
(1 row)

If the same statement specifies precision more than once, Vertica uses the lesser precision. For example, the following statement specifies precision twice: the INTERVAL keyword specifies precision of 1, while the interval qualifier SECOND specifies precision of 2. Vertica uses the lesser precision of 1:

=> SELECT INTERVAL(1) '1.2467' SECOND(2);
 ?column?
----------
 1.2 secs

Setting Precision on Interval Table Columns

If you create a table with an interval column, the following restrictions apply to the column definition:

• You can set precision on the INTERVAL keyword only if you omit specifying an interval qualifier. If you try to set precision on the INTERVAL keyword and include an interval qualifier, Vertica returns an error.
• You can set precision only on the last unit of an interval qualifier. For example:

  CREATE TABLE public.testint2
  (
      i INTERVAL HOUR TO SECOND(3)
  );
  

  If you specify precision on another unit, Vertica discards it when it saves the table definition.