lttb()

Largest Triangle Three Buckets is a downsampling method that tries to retain visual similarity between the downsampled data and the original dataset. The TimescaleDB Toolkit implementation of this takes (timestamp, value) pairs, sorts them if needed, and downsamples them.

Required arguments

NameTypeDescription
timeTIMESTAMPTZTime (x) value for the data point
valueDOUBLE PRECISIONData (y) value for the data point
resolutionINTEGERNumber of points the output should have

Returns

ColumnTypeDescription
timevectorTimevectorA timevector object containing the downsampled points. It can be unpacked with unnest.

Sample usage

This examples uses a table with raw data generated as a sine wave:

  2. SET TIME ZONE 'UTC';
  4. CREATE TABLE metrics(date TIMESTAMPTZ, reading DOUBLE PRECISION);
  6. INSERT INTO metrics
  8. SELECT
  10.     '2020-1-1 UTC'::timestamptz + make_interval(hours=>foo),
  12.     (5 + 5 * sin(foo / 24.0 * PI()))
  14.     FROM generate_series(1,168) foo;

You can use LTTB to dramatically reduce the number of points while still capturing the peaks and valleys in the data:

  2. SELECT time, value
  4. FROM unnest((
  6.     SELECT lttb(date, reading, 8)
  8.     FROM metrics))

The output looks like this:

  2. time          |        value        
  5. ------------------------+---------------------
  7.  2020-01-01 01:00:00+00 |   5.652630961100257
  9.  2020-01-01 13:00:00+00 |   9.957224306869053
  11.  2020-01-02 11:00:00+00 | 0.04277569313094798
  13.  2020-01-03 11:00:00+00 |   9.957224306869051
  15.  2020-01-04 13:00:00+00 | 0.04277569313094709
  17.  2020-01-05 16:00:00+00 |   9.330127018922191
  19.  2020-01-06 20:00:00+00 |  2.4999999999999996
  21.  2020-01-08 00:00:00+00 |   5.000000000000004