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))