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.

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

An INTERVAL can include only the subset of units that you need; however, year/month intervals represent calendar years and months with no fixed number of days, so year/month interval values cannot include days, hours, minutes. When year/month values are specified for day/time intervals, the intervals extension assumes 30 days per month and 365 days per year. Since the length of a given month or year varies, day/time intervals are never output as months or years, only as days, hours, minutes, and so on.
Day/time and year/month intervals are logically independent and cannot be combined with or compared to each other. In the following example, an interval-literal that contains DAYS cannot be combined with the YEAR TO MONTH type:
```
=> SELECT INTERVAL '1 2 3' YEAR TO MONTH;
ERROR 3679:  Invalid input syntax for interval year to month: "1 2 3"
```
Vertica accepts intervals up to 2^63 – 1 microseconds or months (about 18 digits).
INTERVAL YEAR TO MONTH can be used in an analytic RANGE window when the ORDER BY column type is TIMESTAMP/TIMESTAMP WITH TIMEZONE, or DATE. Using TIME/TIME WITH TIMEZONE are not supported.
You can use INTERVAL DAY TO SECOND when the ORDER BY column type is TIMESTAMP/TIMESTAMP WITH TIMEZONE, DATE, and TIME/TIME WITH TIMEZONE.

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).

SELECT INTERVAL '00:2500:00';

1 17:40

SELECT INTERVAL '2500' MINUTE TO SECOND;

SELECT INTERVAL '2500' MINUTE;

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';

SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL '01:00:01.234567';

SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL '01:00:01.234567' HOUR TO SECOND(3);

SELECT INTERVAL(3) '01:00:01.234567' = INTERVAL '01:00:01.234567'MINUTE TO SECOND(3);

SELECT INTERVAL '255 1.1111' MINUTE TO SECOND(3);

255:01.111

SELECT INTERVAL '@ - 5 ago';

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);

SELECT FLOOR((TIMESTAMP '2005-01-17 10:00' - TIMESTAMP '2005-01-01') / INTERVAL '7');