Skip to main content
Skip to main content

Time series functions

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

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

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

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

timeSeriesCopyTag

Introduced in: v26.1

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

timeSeriesCopyTags

Introduced in: v26.1

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

timeSeriesExtractTag

Introduced in: v26.1

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                                │ ᴺᵁᴸᴸ                                    │
└───────┴─────────────────────────────────────────┴────────────────────────────────────┴─────────────────────────────────────────┘

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

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

timeSeriesGroupToTags

Introduced in: v26.1

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

Syntax

timeSeriesGroupToTags(group)

Aliases: timeSeriesTagsGroupToTags

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

timeSeriesIdToGroup

Introduced in: v26.1

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

Syntax

timeSeriesIdToGroup(id)

Aliases: timeSeriesIdToTagsGroup

Arguments

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

timeSeriesIdToTags

Introduced in: v25.8

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

Syntax

timeSeriesIdToTags(id)

Arguments

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

timeSeriesJoinTags

Introduced in: v26.1

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

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

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

timeSeriesRemoveAllTagsExcept

Introduced in: v26.1

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

timeSeriesRemoveTag

Introduced in: v26.1

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

timeSeriesRemoveTags

Introduced in: v26.1

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

timeSeriesReplaceTag

Introduced in: v26.1

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

timeSeriesStoreTags

Introduced in: v25.8

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

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

timeSeriesTagsToGroup

Introduced in: v26.1

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

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