# timeSeriesInstantDeltaToGrid (/docs/reference/sql/aggregate-functions/timeseriesinstantdeltatogrid)



This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See [SQL interface scope](/docs/reference/clickhouse-compatibility#service-interfaces).

Aggregate function that takes time series data as pairs of timestamps and values and calculates [PromQL-like idelta](https://prometheus.io/docs/prometheus/latest/querying/functions/#idelta) from this data on a regular time grid described by start timestamp, end timestamp and step.
For each point on the grid the samples for calculating `idelta` are considered within the specified time window.

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.

:::warning
This function is experimental, enable it by setting `allow_experimental_time_series_aggregate_functions=true`.
:::

**Syntax**

```sql
timeSeriesInstantDeltaToGrid(start_timestamp, end_timestamp, grid_step, staleness)(timestamp, value)
```

**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`](/reference/data-types/int-uint) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`Float*`](/reference/data-types/float) or [`Decimal*`](/reference/data-types/decimal) or [`String`](/reference/data-types/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`](/reference/data-types/int-uint) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`Float*`](/reference/data-types/float) or [`Decimal*`](/reference/data-types/decimal) or [`String`](/reference/data-types/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`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal*`](/reference/data-types/decimal) or [`String`](/reference/data-types/string)
* `staleness` — Specifies the maximum staleness in seconds of the considered samples. The staleness window is a left-open and right-closed interval. 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`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal*`](/reference/data-types/decimal) or [`String`](/reference/data-types/string)

**Arguments**

* `timestamp` — Timestamp of the sample. Can be individual values or arrays. [`UInt32`](/reference/data-types/int-uint) or [`DateTime`](/reference/data-types/datetime) or [`Array(UInt32)`](/reference/data-types/array) or [`Array(DateTime)`](/reference/data-types/array)
* `value` — Value of the time series corresponding to the timestamp. Can be individual values or arrays. [`Float*`](/reference/data-types/float) or [`Array(Float*)`](/reference/data-types/array)

**Returned value**

Returns idelta values on the specified grid. The returned array contains one value for each time grid point. The value is NULL if there are not enough samples within the window to calculate the instant delta value for a particular grid point. [`Array(Nullable(Float64))`](/reference/data-types/array)

**Examples**

**Basic usage with individual timestamp-value pairs**

```sql title=Query
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 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
    45 AS window_seconds  -- "staleness" window
SELECT timeSeriesInstantDeltaToGrid(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
);
```

```text title=Response
┌─timeSeriesInstantDeltaToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)─┐
│ [NULL,NULL,0,2,1,1,NULL,NULL,3]                                                                │
└────────────────────────────────────────────────────────────────────────────────────────────────┘
```

**Using array arguments**

```sql title=Query
SET allow_experimental_time_series_aggregate_functions = 1;
-- it is possible to pass multiple samples of timestamps and values as Arrays of equal size
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,
    45 AS window_seconds
SELECT timeSeriesInstantDeltaToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values);
```

```text title=Response
┌─timeSeriesInstantDeltaToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values)─┐
│ [NULL,NULL,0,2,1,1,NULL,NULL,3]                                                                  │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘
```

*Introduced in version 25.6.0.*
