Skip to content

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.

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

The syntax is:

gp_lttb(
ts TIMESTAMPTZ,
value DOUBLE PRECISION,
[gapsize INTERVAL,]
resolution INT
) RETURNS Timevector
NameTypeDefaultRequiredDescription
tsTIMESTAMPTZ-✔Timestamps for each data point. Must not be NULL.
valueDOUBLE PRECISION-✔The value at each timestamp. Rows with a NULL value are skipped.
gapsizeINTERVAL-A gap between consecutive points larger than gapsize splits the input into segments that are downsampled separately. A gap equal to gapsize does not split. If omitted, gapsize is the time range of the input divided by resolution.
resolutionINT-✔The target number of points to return. Must be greater than 2. Every segment keeps at least its first and last point, so the result can have more points than resolution.
ColumnTypeDescription
gp_lttbTimevectorA timevector of the selected input points, sorted by time. Unpack it with unnest. For more information, see the documentation on timevectors.