Time Series

seriesDecomposeSTL

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
    ]]                                                                                                                   │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 24.1.

seriesOutliersDetectTukey

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] │
└──────────────────────────────────────┘

Introduced in version 24.2.

seriesPeriodDetectFFT

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 │
└─────────┘

Introduced in version 23.12.

timeSeriesCopyTag

Copies a specified tag from one group of tags (src_group) to another (dest_group). The function replaces any previous values of the copied tag in dest_group. If the copied tag is not present in src_group, then the function will remove it from dest_group as well. The function mimics the copying logic of the prometheus group left/group right modifiers.

Syntax

timeSeriesCopyTag(dest_group, src_group, tag_to_copy)

Arguments

dest_group — The destination group of tags. UInt64
src_group — The source group of tags. UInt64
tag_to_copy — The name of a tag to copy. String

Returned value

Returns a group of tags containing the tags from dest_group along with the copied tags from src_group. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS dest_group,
       timeSeriesTagsToGroup([('code', '404'), ('message', 'Page not found')], '__name__', 'http_codes') AS src_group,
       timeSeriesCopyTag(dest_group, src_group, '__name__') AS result_group,
       timeSeriesGroupToTags(result_group)

┌─dest_group─┬─src_group─┬─result_group─┬─timeSeriesGroupToTags(result_group)────────────────────────┐
│          1 │         2 │            3 │ [('__name__','http_codes'),('code','404'),('region','eu')] │
└────────────┴───────────┴──────────────┴────────────────────────────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesCopyTags

Copies specified tags from one group of tags (src_group) to another (dest_group). The function replaces any previous values of the copied tags in dest_group. If some of the copied tags don't present in src_group then the function will remove them in dest_group as well. The function mimics the copying logic of the prometheus group left/group right modifiers.

Syntax

timeSeriesCopyTags(dest_group, src_group, tags_to_copy)

Arguments

dest_group — The destination group of tags. UInt64
src_group — The source group of tags. UInt64
tags_to_copy — The names of tags to copy. Array(String)

Returned value

Returns a group of tags containing the tags from dest_group along with the copied tags from src_group. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS dest_group,
       timeSeriesTagsToGroup([('code', '404'), ('message', 'Page not found')], '__name__', 'http_codes') AS src_group,
       timeSeriesCopyTags(dest_group, src_group, ['__name__', 'code', 'env']) AS result_group,
       timeSeriesGroupToTags(result_group)

┌─dest_group─┬─src_group─┬─result_group─┬─timeSeriesGroupToTags(result_group)────────────────────────┐
│          1 │         2 │            3 │ [('__name__','http_codes'),('code','404'),('region','eu')] │
└────────────┴───────────┴──────────────┴────────────────────────────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesExtractTag

Extracts the value of a specified tag from the group. Returns NULL if not found. See also function timeSeriesGroupToTags().

Syntax

timeSeriesExtractTag(group)

Arguments

group — A group of tags. UInt64
tag_to_extract — The name of a tag to extract from the group String

Returned value

Returns the value of a specified tag. Nullable(String)

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS group,
       timeSeriesExtractTag(group, '__name__'),
       timeSeriesExtractTag(group, 'env'),
       timeSeriesExtractTag(group, 'instance')

┌─group─┬─timeSeriesExtractTag(group, '__name__')─┬─timeSeriesExtractTag(group, 'env')─┬─timeSeriesExtractTag(group, 'instance')─┐
│     1 │ http_requests_count                     │ dev                                │ ᴺᵁᴸᴸ                                    │
└───────┴─────────────────────────────────────────┴────────────────────────────────────┴─────────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesFromGrid

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)] │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 25.8.

timeSeriesGroupToTags

Returns the names and values of the tags associated with a specified group. See also function timeSeriesTagsToGroup().

Syntax

timeSeriesGroupToTags(group)

Arguments

group — A group of tags. UInt64

Returned value

Returns an array of pairs (tag_name, tag_value). The returned array is always sorted by tag_name and never contains the same tag_name more than once. Array(Tuple(String, String))

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS group,
       timeSeriesGroupToTags(group) AS sorted_tags,
       timeSeriesTagsToGroup(sorted_tags) AS same_group,
       throwIf(same_group != group)

┌─group─┬─sorted_tags────────────────────────────────────────────────────────┬─same_group─┬─throwIf(notE⋯up, group))─┐
│     1 │ [('__name__','http_requests_count'),('env','dev'),('region','eu')] │          1 │                        0 │
└───────┴────────────────────────────────────────────────────────────────────┴────────────┴──────────────────────────┘

Introduced in version 26.1.

timeSeriesIdToGroup

Returns the names and values of the tags associated with a specified identifier of a time series. See also function timeSeriesStoreTags().

Syntax

timeSeriesIdToGroup(id)

Arguments

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

Returned value

Returns a group of tags associated with the identifier id of a time series. UInt64

Examples

Example

SELECT 8374283493092 AS id,
       timeSeriesStoreTags(id, [('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS same_id,
       throwIf(same_id != id),
       timeSeriesIdToGroup(same_id) AS group,
       timeSeriesGroupToTags(group)

┌────────────id─┬───────same_id─┬─throwIf(notE⋯me_id, id))─┬─group─┬─timeSeriesGroupToTags(group)───────────────────────────────────────┐
│ 8374283493092 │ 8374283493092 │                        0 │     1 │ [('__name__','http_requests_count'),('env','dev'),('region','eu')] │
└───────────────┴───────────────┴──────────────────────────┴───────┴────────────────────────────────────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesIdToTags

Returns tags associated with a specified identifier of a time series. See also function timeSeriesStoreTags().

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). The returned array is always sorted by tag_name and never contains the same tag_name more than once. Array(Tuple(String, String))

Examples

Example

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

┌────────────id─┬───────same_id─┬─throwIf(notE⋯me_id, id))─┬─timeSeriesIdToTags(same_id)────────────────────────────────────────┐
│ 8374283493092 │ 8374283493092 │                        0 │ [('__name__','http_requests_count'),('env','dev'),('region','eu')] │
└───────────────┴───────────────┴──────────────────────────┴────────────────────────────────────────────────────────────────────┘

Introduced in version 25.8.

timeSeriesJoinTags

Joins the values of specified tags extracted from a group of tags. The function inserts a separator between joined values and returns a new group of tags with the tag dest_tag set to the joined value. This function mimics the logic of the prometheus function label_join().

Syntax

timeSeriesJoinTags(group, dest_tag, separator, src_tags)

Arguments

group — A group of tags. UInt64
dest_tag — The name of a tag with the joined result which will be added to the group. String
separator — A separator to insert between joined values. String
src_tags — The names of source tags with values which will be joined. Array(String)

Returned value

Returns a new group of tags with the dest_tag tag set to the joined result. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('__name__', 'up'), ('job', 'api-server'), ('src1', 'a'), ('src2', 'b'), ('src3', 'c')]) AS group,
       timeSeriesJoinTags(group, 'foo', ',', ['src1', 'src2', 'src3']) AS result_group,
       timeSeriesGroupToTags(result_group)

┌─group─┬─result_group─┬─timeSeriesGroupToTags(result_group)─────────────────────────────────────────────────────────────┐
│     1 │            2 │ [('__name__','up'),('foo','a,b,c'),('job','api-server'),('src1','a'),('src2','b'),('src3','c')] │
└───────┴──────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesRange

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'] │
└───────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 25.8.

timeSeriesRemoveAllTagsExcept

Removes all tags except specified ones from a group of tags. See also function timeSeriesRemoveTag(), timeSeriesRemoveTags().

Syntax

timeSeriesRemoveAllTagsExcept(group, tags_to_keep)

Arguments

group — A group of tags. UInt64
tags_to_keep — The names of tags to keep in the group. Array(String)

Returned value

A new group of tags with only the specified tags kept. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS group,
       timeSeriesRemoveAllTagsExcept(group, ['env']) AS result_group,
       timeSeriesGroupToTags(result_group)

┌─group─┬─result_group─┬─timeSeriesGroupToTags(result_group)─┐
│     1 │            2 │ [('env','dev')]                     │
└───────┴──────────────┴─────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesRemoveTag

Removes a specified tag from a group of tags. If there is no such tag in the group then the group is returned unchanged. See also function timeSeriesRemoveTags(), timeSeriesRemoveAllTagsExcept().

Syntax

timeSeriesRemoveTag(group, tag_to_remove)

Arguments

group — A group of tags. UInt64
tag_to_remove — The name of a tag to remove from the group. String

Returned value

A new group of tags without the specified tag. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS group_of_3,
       timeSeriesRemoveTag(group_of_3, '__name__') AS group_of_2,
       timeSeriesGroupToTags(group_of_2),
       timeSeriesRemoveTag(group_of_2, 'env') AS group_of_1,
       timeSeriesGroupToTags(group_of_1),
       timeSeriesRemoveTag(group_of_1, 'region') AS empty_group,
       timeSeriesGroupToTags(empty_group)

┌─group_of_3─┬─group_of_2─┬─timeSeriesGroupToTags(group_of_2)─┬─group_of_1─┬─timeSeriesGroupToTags(group_of_1)─┬─empty_group─┬─timeSeriesGroupToTags(empty_group)─┐
│          1 │          2 │ [('env','dev'),('region','eu')]   │          3 │ [('region','eu')]                 │           0 │ []                                 │
└────────────┴────────────┴───────────────────────────────────┴────────────┴───────────────────────────────────┴─────────────┴────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesRemoveTags

Removes specified tags from a group of tags. If some of the specified tags are not in the group of tags the function ignores them. See also function timeSeriesRemoveTag(), timeSeriesRemoveAllTagsExcept().

Syntax

timeSeriesRemoveTags(group, tags_to_remove)

Arguments

group — A group of tags. UInt64
tags_to_remove — The names of tags to remove from the group. Array(String)

Returned value

A new group of tags without the specified tags. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS group_of_3,
       timeSeriesRemoveTags(group_of_3, ['env', 'region']) AS group_of_1,
       timeSeriesGroupToTags(group_of_1),
       timeSeriesRemoveTags(group_of_1, ['__name__', 'nonexistent']) AS empty_group,
       timeSeriesGroupToTags(empty_group)

┌─group_of_3─┬─group_of_1─┬─timeSeriesGroupToTags(group_of_1)────┬─empty_group─┬─timeSeriesGroupToTags(empty_group)─┐
│          1 │          2 │ [('__name__','http_requests_count')] │           0 │ []                                 │
└────────────┴────────────┴──────────────────────────────────────┴─────────────┴────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesReplaceTag

Matches the regular expression regex against the value of the tag src_tag. If it matches, the value of the tag dest_tag in the returned group will be the expansion of replacement, together with the original tags in the input. This function mimics the logic of the prometheus function label_replace().

Syntax

timeSeriesReplaceTag(group, dest_tag, replacement, src_tag, regex)

Arguments

group — A group of tags. UInt64
dest_tag — The name of a destination tag to get the result group. String
replacement — A replacement pattern, can contain $1, $2 or $name to refer capturing groups in the regular expression 'regex'. String
src_tag — The name of a tag which value is used to match the regular expression 'regex'. String
regex — A regular expression. String

Returned value

A new group of tags with maybe dest_tag added. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('__name__', 'up'), ('job', 'api-server'), ('service', 'a:c')]) AS group,
       timeSeriesReplaceTag(group, 'foo', '$1', 'service', '(.*):.*') AS result_group,
       timeSeriesGroupToTags(result_group)

┌─group─┬─result_group─┬─timeSeriesGroupToTags(result_group)────────────────────────────────────┐
│     1 │            2 │ [('__name__','up'),('foo','a'),('job','api-server'),('service','a:c')] │
└───────┴──────────────┴────────────────────────────────────────────────────────────────────────┘

Introduced in version 26.1.

timeSeriesStoreTags

Stores in the query context a mapping between a specified identifier of a time series and a set of tags. Functions timeSeriesIdToTags() and timeSeriesIdToGroup() can be used to access this mapping later during the query execution.

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 identifier of a time series (i.e. just the first argument).

Examples

Example

SELECT 8374283493092 AS id,
       timeSeriesStoreTags(id, [('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS same_id,
       throwIf(same_id != id),
       timeSeriesIdToTags(same_id),
       timeSeriesGroupToTags(timeSeriesIdToGroup(same_id))

┌────────────id─┬───────same_id─┬─throwIf(notEquals(same_id, id))─┬─timeSeriesIdToTags(same_id)────────────────────────────────────────┬─timeSeriesGroupToTags(timeSeriesIdToGroup(same_id))────────────────┐
│ 8374283493092 │ 8374283493092 │                               0 │ [('__name__','http_requests_count'),('env','dev'),('region','eu')] │ [('__name__','http_requests_count'),('env','dev'),('region','eu')] │
└───────────────┴───────────────┴─────────────────────────────────┴────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────┘

Introduced in version 25.8.

timeSeriesTagsToGroup

Returns a group of tags associated with specified tags. If the same group of tags is found multiple times during the query execution, the function returns the same group. For an empty set of tags the function always returns 0. See also function timeSeriesGroupToTags().

Syntax

timeSeriesTagsToGroup(tags_array, tag_name_1, tag_value_1, tag_name2, tag_value2, ...)

Arguments

tags_array — Array of pairs (tag_name, tag_value). Array(Tuple(String, String)) or NULL
tag_name_i — The name of a tag. String or FixedString
tag_value_i — The value of a tag. String or FixedString or Nullable(String)

Returned value

Returns a group of tags associated with the specified tags. UInt64

Examples

Example

SELECT timeSeriesTagsToGroup([('region', 'eu'), ('env', 'dev')], '__name__', 'http_requests_count') AS group1,
       timeSeriesTagsToGroup([], '__name__', 'http_failures') AS group2,
       timeSeriesTagsToGroup([]) AS empty_group,
       timeSeriesTagsToGroup([], '__name__', 'http_failures') AS same_group2,
       throwIf(same_group2 != group2),
       timeSeriesGroupToTags(group2)

┌─group1─┬─group2─┬─empty_group─┬─same_group2─┬─throwIf(notEquals(same_group2, group2))─┬─timeSeriesGroupToTags(group2)──┐
│      1 │      2 │           0 │           2 │                                       0 │ [('__name__','http_failures')] │
└────────┴────────┴─────────────┴─────────────┴─────────────────────────────────────────┴────────────────────────────────┘

Introduced in version 26.1.

On this page