Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

timeSeriesResampleToGridWithStaleness

View as MarkdownEdit this pageReport an issueSet up docs MCP

timeSeriesResampleToGridWithStaleness

Introduced in: v25.6.0

Aggregate function that takes time series data as pairs of timestamps and values and re-samples this data to a regular time grid described by start timestamp, end timestamp and step. For each point on the grid the most recent (within the specified time window) sample is chosen.

Alias: timeSeriesLastToGrid.

The samples can be passed in one of three forms:

  • as two arguments timestamp and value, where each row holds a single sample;
  • as two arrays of timestamps and values, where each row holds a whole time series;
  • as a single array of (timestamp, value) tuples, where each row holds a whole time series.

If several samples have the same timestamp, only one of them is used: the sample with the greatest value. A NaN value loses to any other value, so a NaN value is used only if all samples at this timestamp are NaN.

Syntax

timeSeriesResampleToGridWithStaleness(start_timestamp, end_timestamp, grid_step, staleness_window)(timestamp, value)
timeSeriesResampleToGridWithStaleness(start_timestamp, end_timestamp, grid_step, staleness_window)(samples)

Aliases: timeSeriesLastToGrid

Parameters

  • start_timestamp — Specifies start of the grid. With a DateTime64 timestamp argument it can also be a fractional number, or a string containing a number or a date-time text. UInt32 or DateTime or DateTime64 or Float* or Decimal* or String
  • end_timestamp — Specifies end of the grid. With a DateTime64 timestamp argument it can also be a fractional number, or a string containing a number or a date-time text. UInt32 or DateTime or DateTime64 or Float* or Decimal* or String
  • grid_step — Specifies step of the grid in seconds. With a DateTime64 timestamp argument it can also be a fractional number, or a string containing a number or a duration like ‘15s’ or ‘1m’. UInt32 or Float* or Decimal* or String
  • staleness_window — Specifies the maximum staleness of the most recent sample in seconds. With a DateTime64 timestamp argument it can also be a fractional number, or a string containing a number or a duration like ‘15s’ or ‘1m’. UInt32 or Float* or Decimal* or String

Arguments

  • timestamp — Timestamp of the sample. Can be individual values or arrays. UInt32 or DateTime or DateTime64 or Array(UInt32) or Array(DateTime) or Array(DateTime64)
  • value — Value of the time series corresponding to the timestamp. Can be individual values or arrays. Float* or Array(Float*)
  • samples — Samples of the time series passed as an array of tuples (timestamp, value), where the tuple elements have the timestamp and value types listed above. An alternative to passing the timestamps and the values as two separate arguments. Array(Tuple(T1, T2))

Returned value

Returns time series values re-sampled to the specified grid. The returned array contains one value for each time grid point. The value is NULL if there is no sample for a particular grid point. Array(Nullable(Float64))

Examples

Basic usage with individual timestamp-value pairs

SET allow_experimental_time_series_aggregate_functions = 1;
WITH
    -- NOTE: the gap between 140 and 190 is to show how values are filled for ts = 150, 165, 180 according to staleness window parameter
    [110, 120, 130, 140, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 1, 3, 4, 5, 5, 8, 12, 13]::Array(Float32) AS values, -- array of values corresponding to timestamps above
    90 AS start_ts,       -- start of timestamp grid
    90 + 120 AS end_ts,   -- end of timestamp grid
    15 AS step_seconds,   -- step of timestamp grid
    30 AS window_seconds  -- "staleness" window
SELECT timeSeriesResampleToGridWithStaleness(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)
FROM
(
    -- This subquery converts arrays of timestamps and values into rows of `timestamp`, `value`
    SELECT
        arrayJoin(arrayZip(timestamps, values)) AS ts_and_val,
        ts_and_val.1 AS timestamp,
        ts_and_val.2 AS value
);
┌─timeSeriesResampleToGridWithStaleness(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)─┐
│ [NULL,NULL,1,3,4,4,NULL,5,8]                                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Using array arguments

SET allow_experimental_time_series_aggregate_functions = 1;
WITH
    [110, 120, 130, 140, 190, 200, 210, 220, 230]::Array(DateTime) AS timestamps,
    [1, 1, 3, 4, 5, 5, 8, 12, 13]::Array(Float32) AS values,
    90 AS start_ts,
    90 + 120 AS end_ts,
    15 AS step_seconds,
    30 AS window_seconds
SELECT timeSeriesResampleToGridWithStaleness(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values);
┌─timeSeriesResampleToGridWithStaleness(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values)─┐
│ [NULL,NULL,1,3,4,4,NULL,5,8]                                                                              │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Navigation