Time series functions

Below functions are designed to be used with timeSeries*() aggregate functions like timeSeriesInstantRateToGrid, timeSeriesLastToGrid, and so on.

timeSeriesRange

Generates a range of timestamps.

Syntax

timeSeriesRange(start_timestamp, end_timestamp, step)

Arguments

start_timestamp - Start of the range.
end_timestamp - End of the range.
step - Step of the range in seconds.

Returned value

Returns a range of timestamps [start_timestamp, start_timestamp + step, start_timestamp + 2 * step, ..., end_timestamp].

Examples

Query:

SELECT timeSeriesRange('2025-06-01 00:00:00'::DateTime64(3), '2025-06-01 00:01:00'::DateTime64(3), 30) AS rng;

Result:

┌────────────────────────────────────result─────────────────────────────────────────┐
│ ['2025-06-01 00:00:00.000', '2025-06-01 00:00:30.000', '2025-06-01 00:01:00.000'] │
└───────────────────────────────────────────────────────────────────────────────────┘

Notes

If function timeSeriesRange() is called with start_timestamp equal to end_timestamp then it returns a 1-element array containing that timestamp: [start_timestamp]
Function timeSeriesRange() is similar to function range. For example, if the type of timestamps is DateTime64(3) and start_timestamp < end_timestamp then timeSeriesRange(start_timestamp, end_timestamp, step) returns the same result as the following expression:

range(start_timestamp::Int64, end_timestamp::Int64 + 1, step::Int64)::Array(DateTime64(3))

timeSeriesFromGrid

Converts array of values [value1, value2, value3, ..., valueN] to array of tuples [(start_timestamp, value1), (start_timestamp + step, value2), (start_timestamp + 2 * step, value3), ..., (end_timestamp, valueN)].

If some of the values [value1, value2, value3, ...] are NULL then the function won't copy such null values to the result array but will still increase the current timestamp, i.e. for example for [value1, NULL, value2] the function will return [(start_timestamp, value1), (start_timestamp + 2 * step, value2)].

The current timestamp is increased by step until it becomes greater than end_timestamp, each timestamp will be combined with a value from a specified array of values. If number of the values doesn't match number of the timestamps the function will throw an exception.

Syntax

timeSeriesFromGrid(start_timestamp, end_timestamp, step, values);

Arguments

start_timestamp - Start of the grid.
end_timestamp - End of the grid.
step - Step of the grid in seconds.
values - Array of values [value1, value2, ..., valueN].

Returned value

Returns values from the source array of values combined with timestamps on a regular time grid described by start_timestamp and step.

Examples

Query:

SELECT timeSeriesFromGrid('2025-06-01 00:00:00'::DateTime64(3), '2025-06-01 00:01:30.000'::DateTime64(3), 30, [10, 20, NULL, 30]) AS result;

Result:

┌─────────────────────────────────────────────result─────────────────────────────────────────────┐
│ [('2025-06-01 00:00:00.000',10),('2025-06-01 00:00:30.000',20),('2025-06-01 00:01:30.000',30)] │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

Note Function timeSeriesFromGrid(start_timestamp, end_timestamp, step, values) returns the same result as the following expression:

arrayFilter(x -> x.2 IS NOT NULL, arrayZip(timeSeriesRange(start_timestamp, end_timestamp, step), values))

seriesDecomposeSTL

Introduced in: v24.1

Decomposes a series data using STL (Seasonal-Trend Decomposition Procedure Based on Loess) into a season, a trend and a residual component.

Syntax

seriesDecomposeSTL(series, period)

Arguments

series — An array of numeric values Array((U)Int8/16/32/64) or Array(Float*)
period — A positive integer UInt8/16/32/64

Returned value

Returns an array of four arrays where the first array includes seasonal components, the second array - trend, the third array - residue component, and the fourth array - baseline(seasonal + trend) component. Array(Array(Float32), Array(Float32), Array(Float32), Array(Float32))

Examples

Decompose series data using STL

SELECT seriesDecomposeSTL([10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34], 3) AS print_0

┌───────────print_0──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [[
        -13.529999, -3.1799996, 16.71,      -13.53,     -3.1799996, 16.71,      -13.53,     -3.1799996,
        16.71,      -13.530001, -3.18,      16.710001,  -13.530001, -3.1800003, 16.710001,  -13.530001,
        -3.1800003, 16.710001,  -13.530001, -3.1799994, 16.71,      -13.529999, -3.1799994, 16.709997
    ],
    [
        23.63,     23.63,     23.630003, 23.630001, 23.630001, 23.630001, 23.630001, 23.630001,
        23.630001, 23.630001, 23.630001, 23.63,     23.630001, 23.630001, 23.63,     23.630001,
        23.630001, 23.63,     23.630001, 23.630001, 23.630001, 23.630001, 23.630001, 23.630003
    ],
    [
        0, 0.0000019073486, -0.0000019073486, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -0.0000019073486, 0,
        0
    ],
    [
        10.1, 20.449999, 40.340004, 10.100001, 20.45, 40.34, 10.100001, 20.45, 40.34, 10.1, 20.45, 40.34,
        10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.1, 20.45, 40.34, 10.100002, 20.45, 40.34
    ]]                                                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

seriesOutliersDetectTukey

Introduced in: v24.2

Detects outliers in series data using Tukey Fences.

Syntax

seriesOutliersDetectTukey(series[, min_percentile, max_percentile, K])

Arguments

series — An array of numeric values. Array((UInt8/16/32/64)) or Array(Float*)
min_percentile — Optional. The minimum percentile to be used to calculate inter-quantile range (IQR). The value must be in range [0.02,0.98]. The default is 0.25. Float*
max_percentile — Optional. The maximum percentile to be used to calculate inter-quantile range (IQR). The value must be in range [0.02,0.98]. The default is 0.75. Float*
K — Optional. Non-negative constant value to detect mild or stronger outliers. The default value is 1.5. Float*

Returned value

Returns an array of the same length as the input array where each value represents score of possible anomaly of corresponding element in the series. A non-zero score indicates a possible anomaly. Array(Float32)

Examples

Basic outlier detection

SELECT seriesOutliersDetectTukey([-3, 2, 15, 3, 5, 6, 4, 5, 12, 45, 12, 3, 3, 4, 5, 6]) AS print_0

┌───────────print_0─────────────────┐
│[0,0,0,0,0,0,0,0,0,27,0,0,0,0,0,0] │
└───────────────────────────────────┘

Custom parameters outlier detection

SELECT seriesOutliersDetectTukey([-3, 2, 15, 3, 5, 6, 4.50, 5, 12, 45, 12, 3.40, 3, 4, 5, 6], 0.2, 0.8, 1.5) AS print_0

┌─print_0──────────────────────────────┐
│ [0,0,0,0,0,0,0,0,0,19.5,0,0,0,0,0,0] │
└──────────────────────────────────────┘

seriesPeriodDetectFFT

Introduced in: v23.12

Finds the period of the given series data using FFT - Fast Fourier transform

Syntax

seriesPeriodDetectFFT(series)

Arguments

series — An array of numeric values. Array((U)Int8/16/32/64) or Array(Float*)

Returned value

Returns a real value equal to the period of series data. NaN when number of data points are less than four. Float64

Examples

Period detection with simple pattern

SELECT seriesPeriodDetectFFT([1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6, 1, 4, 6]) AS print_0

┌───────────print_0──────┐
│                      3 │
└────────────────────────┘

Period detection with complex pattern

SELECT seriesPeriodDetectFFT(arrayMap(x -> abs((x % 6) - 3), range(1000))) AS print_0

┌─print_0─┐
│       6 │
└─────────┘

timeSeriesFromGrid

Introduced in: v25.8

Converts an array of values [x1, x2, x3, ...] to an array of tuples [(start_timestamp, x1), (start_timestamp + step, x2), (start_timestamp + 2 * step, x3), ...].

The current timestamp is increased by step until it becomes greater than end_timestamp If the number of the values doesn't match the number of the timestamps, the function throws an exception.

NULL values in [x1, x2, x3, ...] are skipped but the current timestamp is still incremented. For example, for [value1, NULL, x2] the function returns [(start_timestamp, x1), (start_timestamp + 2 * step, x2)].

Syntax

timeSeriesFromGrid(start_timestamp, end_timestamp, step, values)

Arguments

start_timestamp — Start of the grid. DateTime64 or DateTime or UInt32
end_timestamp — End of the grid. DateTime64 or DateTime or UInt32
step — Step of the grid in seconds Decimal64 or Decimal32 or UInt32/64
values — Array of values Array(Float*) or Array(Nullable(Float*))

Returned value

Returns values from the source array of values combined with timestamps on a regular time grid described by start_timestamp and step. Array(Tuple(DateTime64, Float64))

Examples

Usage example

SELECT timeSeriesFromGrid('2025-06-01 00:00:00'::DateTime64(3), '2025-06-01 00:01:30.000'::DateTime64(3), 30, [10, 20, NULL, 30]) AS result;

┌─────────────────────────────────────────────result─────────────────────────────────────────────┐
│ [('2025-06-01 00:00:00.000',10),('2025-06-01 00:00:30.000',20),('2025-06-01 00:01:30.000',30)] │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

timeSeriesIdToTags

Introduced in: v25.8

Finds tags associated with the specified identifier of a time series.

Syntax

timeSeriesIdToTags(id)

Arguments

id — Identifier of a time series. UInt64 or UInt128 or UUID or FixedString(16)

Returned value

Returns an array of pairs (tag_name, tag_value). Array(Tuple(String, String))

Examples

Example

SELECT timeSeriesStoreTags(8374283493092, [('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS id, timeSeriesIdToTags(id)

8374283493092    [('__name__', ''http_requests_count''), ('env', 'dev'), ('region', 'eu')]

timeSeriesIdToTagsGroup

Introduced in: v25.8

Converts the specified identifier of a time series to its group index. Group indices are numbers 0, 1, 2, 3 associated with each unique set of tags in the context of the currently executed query.

Syntax

timeSeriesIdToTagsGroup(id)

Arguments

id — Identifier of a time series. UInt64 or UInt128 or UUID or FixedString(16)

Returned value

Returns a group index associated with this set of tags. UInt64

Examples

Example

SELECT timeSeriesStoreTags(8374283493092, [('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS id, timeSeriesIdToTagsGroup(id)

8374283493092    0

timeSeriesRange

Introduced in: v25.8

Generates a range of timestamps [start_timestamp, start_timestamp + step, start_timestamp + 2 * step, ..., end_timestamp].

If start_timestamp is equal to end_timestamp, the function returns a 1-element array containing [start_timestamp].

Function timeSeriesRange() is similar to function range.

Syntax

timeSeriesRange(start_timestamp, end_timestamp, step)

Arguments

start_timestamp — Start of the range. DateTime64 or DateTime or UInt32
end_timestamp — End of the range. DateTime64 or DateTime or UInt32
step — Step of the range in seconds UInt32/64 or Decimal32/64

Returned value

Returns a range of timestamps. Array(DateTime64)

Examples

Usage example

SELECT timeSeriesRange('2025-06-01 00:00:00'::DateTime64(3), '2025-06-01 00:01:00'::DateTime64(3), 30)

┌────────────────────────────────────result─────────────────────────────────────────┐
│ ['2025-06-01 00:00:00.000', '2025-06-01 00:00:30.000', '2025-06-01 00:01:00.000'] │
└───────────────────────────────────────────────────────────────────────────────────┘

timeSeriesStoreTags

Introduced in: v25.8

Stores mapping between the identifier of a time series and its tags in the query context, so that function timeSeriesIdToTags() can extract these tags later.

Syntax

timeSeriesStoreTags(id, tags_array, separate_tag_name_1, separate_tag_value_1, ...)

Arguments

id — Identifier of a time series. UInt64 or UInt128 or UUID or FixedString(16)
tags_array — Array of pairs (tag_name, tag_value). Array(Tuple(String, String)) or NULL
separate_tag_name_i — The name of a tag. String or FixedString
separate_tag_value_i — The value of a tag. String or FixedString or Nullable(String)

Returned value

Returns the first argument, i.e. the identifier of a time series.

Examples

Example

SELECT timeSeriesStoreTags(8374283493092, [('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count')

8374283493092

timeSeriesTagsGroupToTags

Introduced in: v25.8

Finds tags associated with a group index. Group indices are numbers 0, 1, 2, 3 associated with each unique set of tags in the context of the currently executed query.

Syntax

timeSeriesTagsGroupToTags(group)

Arguments

group — Group index associated with a time series. UInt64

Returned value

Array of pairs (tag_name, tag_value). Array(Tuple(String, String))

Examples

Example

SELECT timeSeriesStoreTags(8374283493092, [('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS id, timeSeriesIdToTagsGroup(id) AS group, timeSeriesTagsGroupToTags(group)

8374283493092    0    [('__name__', ''http_requests_count''), ('env', 'dev'), ('region', 'eu')]

timeSeriesRange​

timeSeriesFromGrid​

seriesDecomposeSTL​

seriesOutliersDetectTukey​

seriesPeriodDetectFFT​

timeSeriesFromGrid​

timeSeriesIdToTags​

timeSeriesIdToTagsGroup​

timeSeriesRange​

timeSeriesStoreTags​

timeSeriesTagsGroupToTags​

timeSeriesRange

timeSeriesFromGrid

seriesDecomposeSTL

seriesOutliersDetectTukey

seriesPeriodDetectFFT

timeSeriesFromGrid

timeSeriesIdToTags

timeSeriesIdToTagsGroup

timeSeriesRange

timeSeriesStoreTags

timeSeriesTagsGroupToTags