gp_lttb()

Downsample a time series using the Largest Triangle Three Buckets method, while preserving gaps in original data

Early access 1.11.0

Downsample your data with the Largest Triangle Three Buckets algorithm, while preserving gaps in the underlying data. This method is a specialization of the LTTB algorithm.

Arguments

Name	Type	Default	Required	Description
ts	TIMESTAMPTZ	-	✔	Timestamps for each data point
value	DOUBLE PRECISION	-	✔	The value at each timestamp
resolution	INT	-	✔	The approximate number of points to return. Determines the horizontal resolution of the resulting graph.
gapsize	INTERVAL	-		Minimum gap size to divide input on

Returns

Column	Type	Description
gp_lttb	Timevector	An object representing a series of values occurring at set intervals from a starting time. It can be unpacked with unnest. For more information, see the documentation on timevectors.

Samples

This example uses a table with raw data generated as a sine wave, and removes a day from the middle of the data. You can use gap preserving LTTB to downsample the data while keeping the bounds of the missing region.

SET TIME ZONE 'UTC';
CREATE TABLE metrics(date TIMESTAMPTZ, reading DOUBLE PRECISION);
INSERT INTO metrics
SELECT
    '2020-1-1 UTC'::timestamptz + make_interval(hours=>foo),
    (5 + 5 * sin(foo / 24.0 * PI()))
    FROM generate_series(1,168) foo;
DELETE FROM metrics WHERE date BETWEEN '2020-1-4 UTC' AND '2020-1-5 UTC';

SELECT time, value
FROM unnest((
    SELECT toolkit_experimental.gp_lttb(date, reading, 8)
    FROM metrics))

time                   |             value
-----------------------+-------------------
2020-01-01 01:00:00+00 | 5.652630961100257
2020-01-02 12:00:00+00 |                 0
2020-01-03 23:00:00+00 | 5.652630961100255
2020-01-05 01:00:00+00 | 5.652630961100259
2020-01-05 13:00:00+00 | 9.957224306869051
2020-01-06 12:00:00+00 |                 0
2020-01-07 10:00:00+00 |  9.82962913144534
2020-01-08 00:00:00+00 | 5.000000000000004