Interval

A keyword used to specify a period of time, used in the form INTERVAL [TO ]. Such specifications can be used alone or preceded by the word RANGE, to define a time-based window. Day-time intervals are supported; year-month intervals are not supported. INTERVAL cannot be used as a datatype for a column in a table or stream.

Examples of using intervals without RANGE: An example showing use of the TO syntax:

SELECT STREAM DISTINCT ROWTIME, prodId, FLOOR(Orders.ROWTIME TO DAY) FROM Orders

which displays the set of unique products ordered in any given day.

A command example using the INTERVAL keyword in converting intervals to DAY or SECOND to generate reports at various intervals, uneven or even:

; for reporting at uneven intervals, in this case every day at 8am and 4pm.
GROUP BY
FLOOR(B.ROWTIME - INTERVAL '8' HOUR TO DAY),
FLOOR(B.ROWTIME - INTERVAL '16' HOUR TO DAY),
"ticker";
; for reporting at even intervals, in this case six summary reports each minute, for each 10-second interval.
GROUP BY
FLOOR((B.ROWTIME - TIMESTAMP '1970-01-01 00:00:00') SECOND / 10 to second),
"ticker";

Interval operations examples:

INTERVAL '3 4' DAY TO HOUR / 2
(time '12:03:34' - time '11:57:23') minute to second

Examples of using INTERVAL with RANGE:

FROM SALES.BIDS OVER (RANGE INTERVAL '10' SECOND PRECEDING) AS B
WINDOW "last_Hour" AS (RANGE INTERVAL '1' HOUR PRECEDING),
"last_ten_min" AS (RANGE INTERVAL '10' MINUTE PRECEDING);