A keyword used to specify a period of time, used in the form INTERVAL <timeunit> [TO <timeunit>]. 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 <timeunit> TO <timeunit> 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.
FLOOR(B.ROWTIME - INTERVAL '8' HOUR TO DAY),
FLOOR(B.ROWTIME - INTERVAL '16' HOUR TO DAY),
; for reporting at even intervals, in this case six summary reports each minute, for each 10-second interval.
FLOOR((B.ROWTIME - TIMESTAMP '1970-01-01 00:00:00') SECOND / 10 to second),
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);