Fractional Seconds in Interval Units

Vertica supports intervals in milliseconds (hh:mm:ss:ms), where 01:02:03:25 represents 1 hour, 2 minutes, 3 seconds, and 025 milliseconds. Milliseconds are converted to fractional seconds as in the following example, which returns 1 day, 2 hours, 3 minutes, 4 seconds, and 25.5 milliseconds:

=> SELECT INTERVAL '1 02:03:04:25.5';
  ?column?
------------
 1 day 02:03:04.0255

Vertica allows fractional minutes. The fractional minutes are rounded into seconds:

=> SELECT INTERVAL '10.5 minutes';
  ?column?
------------
 00:10:30
=> select interval '10.659 minutes';
  ?column?   
-------------
 00:10:39.54
=> select interval '10.3333333333333 minutes';
 ?column? 
----------
 00:10:20

Considerations

Examples

Examples in this section assume that INTERVALSTYLE is set to PLAIN , so results omit subtype units. Interval values that omit an interval qualifier use the default to DAY TO SECOND(6).

Query Result
SELECT INTERVAL '00:2500:00';
1 17:40
SELECT INTERVAL '2500' MINUTE TO SECOND;
2500
SELECT INTERVAL '2500' MINUTE;
2500
SELECT INTERVAL '28 days 3 hours' HOUR TO SECOND;
675:00
SELECT INTERVAL(3) '28 days 3 hours';
28 03:00
SELECT INTERVAL(3) '28 days 3 hours 1.234567';
28 03:01:14.074
SELECT INTERVAL(3) '28 days 3 hours 1.234567 sec';
28 03:00:01.235
SELECT INTERVAL(3) '28 days 3.3 hours' HOUR TO SECOND;
675:18
SELECT INTERVAL(3) '28 days 3.35 hours' HOUR TO SECOND;
675:21
SELECT INTERVAL(3) '28 days 3.37 hours' HOUR TO SECOND;
675:22:12
SELECT INTERVAL '1.234567 days' HOUR TO SECOND;
29:37:46.5888
SELECT INTERVAL '1.23456789 days' HOUR TO SECOND;
29:37:46.665696
SELECT INTERVAL(3) '1.23456789 days' HOUR TO SECOND;
29:37:46.666
SELECT INTERVAL(3) '1.23456789 days' HOUR TO SECOND(2);
29:37:46.67
SELECT INTERVAL(3) '01:00:01.234567' as "one hour+";
01:00:01.235
SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL(3) '01:00:01.234567';
t
SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL '01:00:01.234567';
f
SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL '01:00:01.234567' HOUR TO SECOND(3);
t
SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL '01:00:01.234567'MINUTE TO SECOND(3);
t
SELECT INTERVAL '255 1.1111' MINUTE TO SECOND(3);
255:01.111
SELECT INTERVAL '@ - 5 ago';
5
SELECT INTERVAL '@ - 5 minutes ago';
00:05
SELECT INTERVAL '@ 5 minutes ago';
-00:05
SELECT INTERVAL '@ ago -5 minutes';
00:05
SELECT DATE_PART('month', INTERVAL '2-3' YEAR TO MONTH);
3
SELECT FLOOR((TIMESTAMP '2005-01-17 10:00' - TIMESTAMP '2005-01-01') / INTERVAL '7');
2