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.
Samples
Section titled “Samples”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 metricsSELECT '2020-1-1 UTC'::timestamptz + make_interval(hours=>foo), (5 + 5 * sin(foo / 24.0 * PI())) FROM generate_series(1,168) foo;
SELECT time, valueFROM unnest(( SELECT lttb(date, reading, 8) FROM metrics))time | value------------------------+---------------------2020-01-01 01:00:00+00 | 5.6526309611002572020-01-01 13:00:00+00 | 9.9572243068690532020-01-02 11:00:00+00 | 0.042775693130947982020-01-03 11:00:00+00 | 9.9572243068690512020-01-04 13:00:00+00 | 0.042775693130947092020-01-05 16:00:00+00 | 9.3301270189221912020-01-06 20:00:00+00 | 2.49999999999999962020-01-08 00:00:00+00 | 5.000000000000004Arguments
Section titled “Arguments”The syntax is:
lttb( ts TIMESTAMPTZ, value DOUBLE PRECISION, resolution INT) RETURNS Timevector| Name | Type | Default | Required | Description |
|---|---|---|---|---|
| ts | TIMESTAMPTZ | - | ✔ | Timestamps for each data point. Must not be NULL. |
| value | DOUBLE PRECISION | - | ✔ | The value at each timestamp. Rows with a NULL value are skipped. |
| resolution | INT | - | ✔ | 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. |
Returns
Section titled “Returns”| Column | Type | Description |
|---|---|---|
| lttb | Timevector | A timevector of the selected input points, sorted by time. Unpack it with unnest. For more information, see the documentation on timevectors. |