Interval

<< Click to Display Table of Contents >>

Navigation:  Glossary >

Interval

Previous pageReturn to chapter overviewNext page

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.

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