Downsampling functions

ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale, but must be installed separately for self-hosted TimescaleDB. Click to learn more.

Downsample your data to visualize trends while preserving fewer data points. Downsampling replaces a set of values with a much smaller set that is highly representative of the original data. This is particularly useful for graphing applications.

Functions in this group

Warning

This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.

asap_smooth: Downsample a time series using the ASAP smoothing algorithm
gp_lttb: ExperimentalDownsample a time series using the Largest Triangle Three Buckets method, while preserving gaps in original data
lttb: Downsample a time series using the Largest Triangle Three Buckets method

Function details

asap_smooth(
  ts TIMESTAMPTZ,
  value DOUBLE PRECISION,
  resolution INT
) RETURNS Timevector

Downsample your data with the ASAP smoothing algorithm. This algorithm preserves the approximate shape and larger trends of the input data, while minimizing the local variance between points.

Required arguments

Name	Type	Description
`ts`	`TIMESTAMPTZ`	Timestamps for each data point
`value`	`DOUBLE PRECISION`	The value at each timestamp
`resolution`	`INT`	The approximate number of points to return. Determines the horizontal resolution of the resulting graph.

Returns

Column	Type	Description
`asap_smooth`	`Timevector`	An object representing a series of values occurring at set intervals from a starting time. It can be unpacked with `unnest`. For more information, see the documentation on timevectors.

Examples

This example uses a table called metrics, with columns for date and reading. The columns contain measurements that have been accumulated over a large interval of time. This example takes that data and provides a smoothed representation of approximately 10 points, but that still shows any anomalous readings::


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 / 12.0 * PI()))
    FROM generate_series(1,168) foo;

SELECT * FROM unnest(
  (SELECT asap_smooth(date, reading, 8)
    FROM metrics)
);


time                    |        value
------------------------+---------------------
2020-01-01 01:00:00+00  | 5.3664814565722665
2020-01-01 21:00:00+00  |  5.949469264090644
2020-01-02 17:00:00+00  |  5.582987807518377
2020-01-03 13:00:00+00  |  4.633518543427733
2020-01-04 09:00:00+00  |  4.050530735909357
2020-01-05 05:00:00+00  |  4.417012192481623
2020-01-06 01:00:00+00  |  5.366481456572268
2020-01-06 21:00:00+00  |  5.949469264090643

gp_lttb(
  ts TIMESTAMPTZ,
  value DOUBLE PRECISION,
  resolution INT
  [, gapsize INTERVAL]
) RETURNS Timevector

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.

Required arguments

Name	Type	Description
`ts`	`TIMESTAMPTZ`	Timestamps for each data point
`value`	`DOUBLE PRECISION`	The value at each timestamp
`resolution`	`INT`	The approximate number of points to return. Determines the horizontal resolution of the resulting graph.

Optional arguments

Name	Type	Description
`gapsize`	`INTERVAL`	Minimum gap size to divide input on

Returns

Column	Type	Description
`gp_lttb`	`Timevector`	An object representing a series of values occurring at set intervals from a starting time. It can be unpacked with `unnest`. For more information, see the documentation on timevectors.

Examples

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

lttb(
  ts TIMESTAMPTZ,
  value DOUBLE PRECISION,
  resolution INT
) RETURNS Timevector

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.

Required arguments

Name	Type	Description
`ts`	`TIMESTAMPTZ`	Timestamps for each data point
`value`	`DOUBLE PRECISION`	The value at each timestamp
`resolution`	`INT`	The approximate number of points to return. Determines the horizontal resolution of the resulting graph.

Returns

Column	Type	Description
`lttb`	`Timevector`	An object representing a series of values occurring at set intervals from a starting time. It can be unpacked with `unnest`. For more information, see the documentation on timevectors.

Examples

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

Downsampling functions

Functions in this group

Warning

Function

Function details

asap_smooth()

gp_lttb()

lttb()

Downsampling functions

Introduction

Functions in this group

Warning

Function

Function details

asap_smooth()

gp_lttb()

lttb()

Related Content