Time window functions

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Time window functions return the inclusive lower and exclusive upper bound of the corresponding window. The functions for working with WindowView are listed below:

hop

Introduced in: v21.12

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.

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.

Syntax

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 — Optional. Timezone name. String

Returned value

Returns the inclusive lower and exclusive upper bound of the corresponding hopping window. Tuple(DateTime, DateTime)

Examples

Hopping window

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

('2024-07-03 00:00:00','2024-07-05 00:00:00')

hopEnd

Introduced in: v22.1

Returns the exclusive upper bound of the corresponding hopping window.

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.

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 — Optional. Timezone name. String

Returned value

Returns the exclusive upper bound of the corresponding hopping window. DateTime

Examples

Hopping window end

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

2024-07-05 00:00:00

hopStart

Introduced in: v22.1

Returns the inclusive lower bound of the corresponding hopping window.

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.

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 — Optional. Timezone name. String

Returned value

Returns the inclusive lower bound of the corresponding hopping window. DateTime

Examples

Hopping window start

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

2024-07-03 00:00:00

tumble

Introduced in: v21.12

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. Interval
• timezone — Optional. Timezone name. String

Returned value

Returns the inclusive lower and exclusive upper bound of the corresponding tumbling window. Tuple(DateTime, DateTime)

Examples

Tumbling window

SELECT tumble(now(), toIntervalDay('1'))

('2024-07-04 00:00:00','2024-07-05 00:00:00')

tumbleEnd

Introduced in: v22.1

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. Interval
• timezone — Optional. Timezone name. String

Returned value

Returns the exclusive upper bound of the corresponding tumbling window. DateTime

Examples

Tumbling window end

SELECT tumbleEnd(now(), toIntervalDay('1'))

2024-07-05 00:00:00

tumbleStart

Introduced in: v22.1

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. Interval
• timezone — Optional. Timezone name. String

Returned value

Returns the inclusive lower bound of the corresponding tumbling window. DateTime

Examples

Tumbling window start

SELECT tumbleStart(now(), toIntervalDay('1'))

2024-07-04 00:00:00

Related content

• Time-series use-case guides