Relational Data - Functions - Time Window Functions
Time window functions return the inclusive lower and exclusive upper bound of the corresponding window. The functions for working with WindowView are listed below:
tumble
A tumbling time window assigns records to non-overlapping, continuous windows with a fixed duration (interval).
Syntax
tumble(time_attr, interval [, timezone])Arguments - time_attr — Date and time. DateTime. - interval — Window interval in Interval. - timezone — Timezone name (optional).
Returned values
- The inclusive lower and exclusive upper bound of the corresponding tumbling window. Tuple(DateTime, DateTime).
Example
Query:
SELECT tumble(now(), toIntervalDay('1'));Result:
┌─tumble(now(), toIntervalDay('1'))─────────────┐
│ ('2024-07-04 00:00:00','2024-07-05 00:00:00') │
└───────────────────────────────────────────────┘
tumbleStart
Returns the inclusive lower bound of the corresponding tumbling window.
Syntax
tumbleStart(time_attr, interval [, timezone]);Arguments
time_attr— Date and time. DateTime.interval— Window interval in Interval.timezone— Timezone name (optional).
The parameters above can also be passed to the function as a tuple.
Returned values
Example
Query:
SELECT tumbleStart(now(), toIntervalDay('1'));Result:
┌─tumbleStart(now(), toIntervalDay('1'))─┐
│ 2024-07-04 00:00:00 │
└────────────────────────────────────────┘
tumbleEnd
Returns the exclusive upper bound of the corresponding tumbling window.
Syntax
tumbleEnd(time_attr, interval [, timezone]);Arguments
time_attr— Date and time. DateTime.interval— Window interval in Interval.timezone— Timezone name (optional).
The parameters above can also be passed to the function as a tuple.
Returned values
Example
Query:
SELECT tumbleEnd(now(), toIntervalDay('1'));Result:
┌─tumbleEnd(now(), toIntervalDay('1'))─┐
│ 2024-07-05 00:00:00 │
└──────────────────────────────────────┘
hop
A hopping time window has a fixed duration (window_interval) and hops by a specified hop interval (hop_interval). If the hop_interval is smaller than the window_interval, hopping windows are overlapping. Thus, records can be assigned to multiple windows.
hop(time_attr, hop_interval, window_interval [, timezone])Arguments
time_attr— Date and time. DateTime.hop_interval— Positive Hop interval. Interval.window_interval— Positive Window interval. Interval.timezone— Timezone name (optional).
Returned values
- The inclusive lower and exclusive upper bound of the corresponding hopping window. Tuple(DateTime, DateTime)`.
Since one record can be assigned to multiple hop windows, the function only returns the bound of the first window when hop function is used without WINDOW VIEW.
Example
Query:
SELECT hop(now(), INTERVAL '1' DAY, INTERVAL '2' DAY);Result:
┌─hop(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│ ('2024-07-03 00:00:00','2024-07-05 00:00:00') │
└────────────────────────────────────────────────────┘
hopStart
Returns the inclusive lower bound of the corresponding hopping window.
Syntax
hopStart(time_attr, hop_interval, window_interval [, timezone]);Arguments
time_attr— Date and time. DateTime.hop_interval— Positive Hop interval. Interval.window_interval— Positive Window interval. Interval.timezone— Timezone name (optional).
The parameters above can also be passed to the function as a tuple.
Returned values
Since one record can be assigned to multiple hop windows, the function only returns the bound of the first window when hop function is used without WINDOW VIEW.
Example
Query:
SELECT hopStart(now(), INTERVAL '1' DAY, INTERVAL '2' DAY);Result:
┌─hopStart(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│ 2024-07-03 00:00:00 │
└─────────────────────────────────────────────────────────┘
hopEnd
Returns the exclusive upper bound of the corresponding hopping window.
Syntax
hopEnd(time_attr, hop_interval, window_interval [, timezone]);Arguments
time_attr— Date and time. DateTime.hop_interval— Positive Hop interval. Interval.window_interval— Positive Window interval. Interval.timezone— Timezone name (optional).
The parameters above can also be passed to the function as a tuple.
Returned values
Since one record can be assigned to multiple hop windows, the function only returns the bound of the first window when hop function is used without WINDOW VIEW.
Example
Query:
SELECT hopEnd(now(), INTERVAL '1' DAY, INTERVAL '2' DAY);Result:
┌─hopEnd(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│ 2024-07-05 00:00:00 │
└───────────────────────────────────────────────────────┘