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

Name Type Description
time TIMESTAMPTZ Time (x) value for the data point
value DOUBLE PRECISION Data (y) value for the data point
resolution INTEGER Number of points the output should have

Returns

Column Type Description
timevector Timevector A 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