Skip to content

lttb()

Downsample a time series using the Largest Triangle Three Buckets method

Since 1.10.1

Downsample your data with the Largest Triangle Three Buckets algorithm. This algorithm tries to retain visual similarity between the downsampled data and the original dataset.

This example uses a table with raw data generated as a sine wave. You can use LTTB to dramatically reduce the number of points while still capturing the peaks and valleys in the data.

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;
SELECT time, value
FROM unnest((
SELECT lttb(date, reading, 8)
FROM metrics))
time | value
------------------------+---------------------
2020-01-01 01:00:00+00 | 5.652630961100257
2020-01-01 13:00:00+00 | 9.957224306869053
2020-01-02 11:00:00+00 | 0.04277569313094798
2020-01-03 11:00:00+00 | 9.957224306869051
2020-01-04 13:00:00+00 | 0.04277569313094709
2020-01-05 16:00:00+00 | 9.330127018922191
2020-01-06 20:00:00+00 | 2.4999999999999996
2020-01-08 00:00:00+00 | 5.000000000000004

The syntax is:

lttb(
ts TIMESTAMPTZ,
value DOUBLE PRECISION,
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.
resolutionINT-✔The number of points to return. Must be greater than 2. If resolution is at least the number of input points, all points are returned.
ColumnTypeDescription
lttbTimevectorA timevector of the selected input points, sorted by time. Unpack it with unnest. For more information, see the documentation on timevectors.