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

• The inclusive lower bound of the corresponding tumbling window. DateTime, Tuple or UInt32.

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

• The inclusive lower bound of the corresponding tumbling window. DateTime, Tuple or UInt32.

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

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

• The inclusive lower bound of the corresponding hopping window. DateTime, Tuple or UInt32.

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

• The exclusive upper bound of the corresponding hopping window. DateTime, Tuple or UInt32.

Example

Query:

SELECT hopEnd(now(), INTERVAL '1' DAY, INTERVAL '2' DAY)

Result:

┌─hopEnd(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│                                   2024-07-05 00:00:00 │
└───────────────────────────────────────────────────────┘