> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Calculates PromQL-like `ts_of_last_over_time` on a regular time grid. # timeSeriesTimestampOfLastToGrid

timeSeriesTimestampOfLastToGrid

Introduced in: v26.10.0 Aggregate function that takes time series data as pairs of timestamps and values and calculates [PromQL-like ts\_of\_last\_over\_time](https://prometheus.io/docs/prometheus/latest/querying/functions/#aggregation_over_time) from this data on a regular time grid described by start timestamp, end timestamp and step. For each point on the grid the result is the timestamp of the most recent sample within the specified time window. 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. This function is in private preview, enable it by setting `enable_time_series_aggregate_functions=true`. **Syntax** ```sql theme={null} timeSeriesTimestampOfLastToGrid(start_timestamp, end_timestamp, grid_step, staleness_window)(timestamp, value) timeSeriesTimestampOfLastToGrid(start_timestamp, end_timestamp, grid_step, staleness_window)(samples) ``` **Parameters** * `start_timestamp` — Specifies start of the grid. 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. 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. 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_window` — Specifies the maximum staleness of the most recent sample in seconds. 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 [`DateTime64`](/reference/data-types/datetime64) or [`Array(UInt32)`](/reference/data-types/array) or [`Array(DateTime)`](/reference/data-types/array) or [`Array(DateTime64)`](/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) * `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))`](/reference/data-types/array) **Returned value** Returns the timestamps of the most recent samples on the specified grid, of the same type as `timestamp`. The returned array contains one value for each time grid point. The value is NULL if there is no sample within the window for a particular grid point. [`Array(Nullable(UInt32))`](/reference/data-types/array) or [`Array(Nullable(DateTime))`](/reference/data-types/array) or [`Array(Nullable(DateTime64))`](/reference/data-types/array) **Examples** **Calculate ts\_of\_last\_over\_time values on the grid \[90, 105, 120, 135, 150, 165, 180, 195, 210]** ```sql title=Query theme={null} SET enable_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 timeSeriesTimestampOfLastToGrid(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 ); ``` ```response title=Response theme={null} ┌─timeSeriesTimestampOfLastToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamp, value)────────────────────────────────────────────────────┐ │ [NULL,NULL,'1970-01-01 00:02:00','1970-01-01 00:02:10','1970-01-01 00:02:20','1970-01-01 00:02:20',NULL,'1970-01-01 00:03:10','1970-01-01 00:03:30'] │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` **Same query with array arguments** ```sql title=Query theme={null} SET enable_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 timeSeriesTimestampOfLastToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values); ``` ```response title=Response theme={null} ┌─timeSeriesTimestampOfLastToGrid(start_ts, end_ts, step_seconds, window_seconds)(timestamps, values)──────────────────────────────────────────────────┐ │ [NULL,NULL,'1970-01-01 00:02:00','1970-01-01 00:02:10','1970-01-01 00:02:20','1970-01-01 00:02:20',NULL,'1970-01-01 00:03:10','1970-01-01 00:03:30'] │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ```