gp_lttb() - Tiger Data

Early access Since 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.

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

Arguments

The syntax is:

gp_lttb(
  ts TIMESTAMPTZ,
  value DOUBLE PRECISION,
  [gapsize INTERVAL,]
  resolution INT
) RETURNS Timevector

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

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.