unnest()

Extract time-value pairs from a timevector back into rows

Early access 1.3.0

Extract the time-value pairs from a timevector object back into individual rows. This accessor function is typically used to view the results of timevector operations or to convert timevector data back into a standard table format.

Samples

Extract data from a timevector

Get the time-value pairs from a timevector:

SELECT time, value
FROM unnest((
    SELECT timevector(time, temperature)
    FROM sensor_data
));

View downsampled results

Extract downsampled data after using LTTB:

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

Limit results

Extract only the first 10 points from a timevector:

SELECT time, value
FROM unnest((
    SELECT timevector(time, value)
    FROM generate_series(
        '2020-01-01'::timestamptz,
        '2020-01-10'::timestamptz,
        '1 hour'::interval
    ) AS time,
    LATERAL (SELECT random() * 100 AS value) v
))
LIMIT 10;

Arguments

The syntax is:

unnest(
    series Timevector
) RETURNS TABLE(time TIMESTAMPTZ, value DOUBLE PRECISION)

Name	Type	Default	Required	Description
series	Timevector	-	✔	The timevector to extract data from

Returns

Column	Type	Description
unnest	TABLE	A table with time (TIMESTAMPTZ) and value (DOUBLE PRECISION) columns containing the time-value pairs from the timevector