Timevector overview

Early access 1.3.0

A timevector is an intermediate representation for efficiently storing and processing time-series data. It provides a space-efficient way to store time-value pairs and supports pipeline operations for common transformations.

Timevectors are used as the return type for analytic functions like lttb() and asap_smooth(), and can be created directly from your data using the timevector() aggregate function.

timevectors give you:

• Space efficient: compact representation of time-value pairs
• Pipeline operations: chain transformations using the -> operator
• Flexible aggregation: create from raw data or combine existing timevectors
• Easy extraction: use unnest() to convert back to rows

Samples

Create and query a timevector

This example creates a timevector from time-series data and extracts the values:

SET TIME ZONE 'UTC';
CREATE TABLE test(time TIMESTAMPTZ, value DOUBLE PRECISION);

INSERT INTO test
    SELECT time, value
    FROM toolkit_experimental.generate_periodic_normal_series('2020-01-01 UTC'::timestamptz, rng_seed => 11111);

-- Create a timevector and store in a view
CREATE VIEW series AS SELECT timevector(time, value) FROM test;

-- Extract data back out
SELECT time, value::numeric(10,2)
FROM unnest((SELECT timevector FROM series))
LIMIT 5;

Use timevector with downsampling

Timevectors work seamlessly with downsampling functions:

SELECT time, value::numeric(10,2)
FROM unnest((
    SELECT lttb(timevector, 20)
    FROM series
));

Combine multiple timevectors

Use rollup() to combine timevectors from different groups:

CREATE TABLE samples(time TIMESTAMPTZ, device_id INT, temperature DOUBLE PRECISION);

-- Create timevectors per device
CREATE VIEW device_series AS
    SELECT device_id, timevector(time, temperature) AS series
    FROM samples
    GROUP BY device_id;

-- Combine all devices into a single timevector
SELECT rollup(series) FROM device_series;

Timevector pipelines

Timevectors support pipeline operations using the -> operator. This allows you to chain transformations:

SELECT timevector(time, value)
    -> toolkit_experimental.sort()
    -> toolkit_experimental.delta()
FROM test;

Common pipeline elements include:

• sort(): Sort points by timestamp
• delta(): Calculate differences between consecutive values
• lttb(resolution): Downsample using LTTB algorithm

Pipeline operations can be grouped with parentheses for optimization:

SELECT timevector(time, value)
    -> (toolkit_experimental.sort() -> toolkit_experimental.delta())
FROM test;

Available functions

Aggregate functions

• timevector(): create a timevector from time-value pairs
• rollup(): combine multiple timevectors

Accessor functions

• unnest(): extract time-value pairs from a timevector