gauge_agg() 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.

Analyze data coming from gauges. Unlike counters, gauges can decrease as well as increase.

If your value can only increase, use counter_agg instead to appropriately account for resets.

Related hyperfunction groups

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

More efficient because multiple accessors can reuse the same aggregate
Easier to reason about performance, because aggregation is separate from final computation
Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

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.

gauge_agg: ExperimentalAggregate gauge data into an intermediate form for further analysis

corr: ExperimentalCalculate the correlation coefficient from a gauge aggregate
delta: ExperimentalCalculate the change in a gauge from a gauge aggregate
extrapolated_delta: ExperimentalCalculate the extrapolated change from a gauge aggregate
extrapolated_rate: ExperimentalCalculate the extrapolated rate of change from a gauge aggregate
gauge_zero_time: ExperimentalCalculate the time when the gauge value is predicted to have been zero
idelta_left: ExperimentalCalculate the instantaneous change at the left, or earliest, edge of a gauge aggregate
idelta_right: ExperimentalCalculate the instantaneous change at the right, or latest, edge of a gauge aggregate
intercept: ExperimentalCalculate the y-intercept from a gauge aggregate
interpolated_delta: ExperimentalCalculate the change in a gauge, interpolating values at boundaries as needed
interpolated_rate: ExperimentalCalculate the rate of change in a gauge, interpolating values at boundaries as needed
irate_left: ExperimentalCalculate the instantaneous rate of change at the left, or earliest, edge of a gauge aggregate
irate_right: ExperimentalCalculate the instantaneous rate of change at the right, or latest, edge of a gauge aggregate
num_changes: ExperimentalGet the number of times a gauge changed from a gauge aggregate
num_elements: ExperimentalGet the number of points with distinct timestamps from a gauge aggregate
rate: ExperimentalCalculate the rate of change from a gauge aggregate
slope: ExperimentalCalculate the slope from a gauge aggregate
time_delta: ExperimentalCalculate the difference between the first and last times from a gauge aggregate

rollup: ExperimentalCombine multiple gauge aggregates

with_bounds: ExperimentalAdd bounds to a gauge aggregate

Function details

gauge_agg(
  ts TIMESTAMPTZ,
  value DOUBLE PRECISION
  [, bounds TSTZRANGE]
) RETURNS GaugeSummary

This is the first step for performing any aggregate calculations on gauge data. Use gauge_agg to create an intermediate aggregate from your data. This intermediate form can then be used by one or more accessors in this group to compute final results. Optionally, you can combine multiple intermediate aggregate objects with rollup() before an accessor is applied.

Required arguments

Name	Type	Description
`ts`	`TIMESTAMPTZ`	The time at each point
`value`	`DOUBLE PRECISION`	The value of the gauge at each point

Optional arguments

Name	Type	Description
`bounds`	`TSTZRANGE`	The smallest and largest possible times that can be input to this aggregate. Bounds are required for extrapolation, but not for other accessor functions. If you don't specify bounds at aggregate creation time, you can add them later using the `with_bounds` function.

Returns

Column	Type	Description
`gauge_agg`	`GaugeSummary`	The gauge aggregate, containing data about the variables in an intermediate form. Pass the aggregate to accessor functions in the gauge aggregates API to perform final calculations. Or, pass the aggregate to rollup functions to combine multiple gauge aggregates into larger aggregates.

Examples

Create a gauge aggregate to summarize daily gauge data:


SELECT
  time_bucket('1 day'::interval, ts) as dt,
  gauge_agg(ts, val) AS cs
FROM foo
WHERE id = 'bar'
GROUP BY time_bucket('1 day'::interval, ts)

corr(
    summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the correlation coefficient from a gauge aggregate. The calculation uses a linear least-squares fit, and returns a value between 0.0 and 1.0, from no correlation to the strongest possible correlation.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`corr`	`DOUBLE PRECISION`	The correlation coefficient calculated with time as the independent variable and gauge value as the dependent variable.

Examples

Calculate the correlation coefficient to determine the goodness of a linear fit between gauge value and time:


SELECT
    id,
    bucket,
    corr(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

delta(
    summary GaugeSummary
) RETURNS DOUBLE PRECISION

Get the change in a gauge over a time period. This is the simple delta, computed by subtracting the last seen value from the first.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregated created using `gauge_agg`

Returns

Column	Type	Description
`delta`	`DOUBLE PRECISION`	The change in the gauge over the bucketed interval

Examples

Get the change in each gauge over the entire time interval in table foo:


SELECT
    id,
    delta(summary)
FROM (
    SELECT
        id,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id
) t

extrapolated_delta(
    summary GaugeSummary,
    method TEXT
) RETURNS DOUBLE PRECISION

Calculate the change in a gauge during the time period specified by the bounds in the gauge aggregate. The bounds must be specified for the extrapolated_delta function to work. You can provide them as part of the original gauge_agg call, or by using the with_bounds function on an existing gauge aggregate.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`
`method`	`TEXT`	The extrapolation method to use. Not case-sensitive. The only allowed value is `prometheus`, for the Prometheus extrapolation protocol.

Returns

Column	Type	Description
`extrapolated_delta`	`DOUBLE PRECISION`	The extrapolated change in the gauge over the time period of the gauge aggregate.

Examples

Extrapolate the change in a gauge over every 15-minute interval:


SELECT
    id,
    bucket,
    extrapolated_delta(
        with_bounds(
            summary,
            toolkit_experimental.time_bucket_range('15 min'::interval, bucket)
        ),'prometheus'
    )
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t;

extrapolated_rate(
    summary GaugeSummary,
    method TEXT
) RETURNS DOUBLE PRECISION

Calculate the rate of change of a gauge during the time period specified by the bounds in the gauge aggregate. The bounds must be specified for the extrapolated_rate function to work. You can provide them as part of the original gauge_agg call, or by using the with_bounds function on an existing gauge aggregate.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`
`method`	`TEXT`	The extrapolation method to use. Not case-sensitive. The only allowed value is `prometheus`, for the Prometheus extrapolation protocol.

Returns

Column	Type	Description
`extrapolated_rate`	`DOUBLE PRECISION`	The extrapolated rate of change of the gauge over the timer period of the gauge aggregate.

Examples


SELECT
    id,
    bucket,
    extrapolated_rate(
        with_bounds(
            summary,
            toolkit_experimental.time_bucket_range('15 min'::interval, bucket)
        ),'prometheus'
    )
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t;

gauge_zero_time(
    summary GaugeSummary
) RETURNS TIMESTAMPTZ

Calculate the time when the gauge value is modeled to have been zero. This is the x-intercept of the linear fit between gauge value and time.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`gauge_zero_time`	`TIMESTAMPTZ`	The time when the gauge value is predicted to have been zero

Examples

Estimate the time when the gauge started:


SELECT
    id,
    bucket,
    gauge_zero_time(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

idelta_left(
  summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the instantaneous change at the left, or earliest, edge of a gauge aggregate. This is equal to the second value minus the first value.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`idelta_left`	`DOUBLE PRECISION`	The instantaneous delta at the left, or earliest, edge of the gauge aggregate

Examples

Get the instantaneous change at the start of each 15-minute gauge aggregate:


SELECT
    id,
    bucket,
    idelta_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

idelta_right(
  summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the instantaneous change at the right, or latest, edge of a gauge aggregate. This is equal to the last value minus the second-last value.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`idelta_right`	`DOUBLE PRECISION`	The instantaneous delta at the right, or latest, edge of the gauge aggregate

Examples

Get the instantaneous change at the end of each 15-minute gauge aggregate:


SELECT
    id,
    bucket,
    idelta_right(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

intercept(
    summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the y-intercept of a linear least-squares fit between gauge value and time. This corresponds to the projected value at the PostgreSQL epoch (2000-01-01 00:00:00+00). You can use the y-intercept with the slope to plot a best-fit line.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`intercept`	`DOUBLE PRECISION`	The y-intercept of the linear least-squares fit

Examples

Calculate the y-intercept of the linear fit for each 15-minute gauge aggregate:


SELECT
    id,
    bucket,
    intercept(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

interpolated_delta(
    summary GaugeSummary,
    start TIMESTAMPTZ,
    interval INTERVAL
    [, prev GaugeSummary]
    [, next GaugeSummary]
) RETURNS DOUBLE PRECISION

Calculate the change in a gauge over the time period covered by a gauge aggregate. Data points at the exact boundaries of the time period aren't needed. The function interpolates the gauge values at the boundaries from adjacent gauge aggregates if needed.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`
`start`	`TIMESTAMPTZ`	The start of the time period to compute the delta over
`interval`	`INTERVAL`	The length of the time period to compute the delta over

Optional arguments

Name	Type	Description
`prev`	`GaugeSummary`	The gauge aggregate from the previous interval, used to interpolate the value at `start`. If `NULL`, the first timestamp in `summary` is used as the start of the interval.
`next`	`GaugeSummary`	The gauge aggregate from the next interval, used to interpolate the value at `start + interval`. If `NULL`, the last timestamp in `summary` is used as the end of the interval.

Returns

Column	Type	Description
`interpolated_delta`	`DOUBLE PRECISION`	The delta between the first and last points of the time interval. If exact values are missing in the raw data for the first and last points, these values are interpolated linearly from the neighboring gauge aggregates.

Examples

Calculate the gauge delta for each 15-minute interval, using interpolation to get the values at the interval boundaries if they don't exist in the data:


SELECT
    id,
    bucket,
    interpolated_delta(
        summary,
        bucket,
        '15 min',
        LAG(summary) OVER (PARTITION BY id ORDER by bucket),
        LEAD(summary) OVER (PARTITION BY id ORDER by bucket)
    )
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

interpolated_rate(
    summary GaugeSummary,
    start TIMESTAMPTZ,
    interval INTERVAL
    [, prev GaugeSummary]
    [, next GaugeSummary]
) RETURNS DOUBLE PRECISION

Calculate the rate of change in a gauge over a time period. Data points at the exact boundaries of the time period aren't needed. The function interpolates the gauge values at the boundaries from adjacent gauge aggregates if needed.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`
`start`	`TIMESTAMPTZ`	The start of the time period to compute the rate over
`interval`	`INTERVAL`	The length of the time period to compute the rate over

Optional arguments

Name	Type	Description
`prev`	`GaugeSummary`	The gauge aggregate from the previous interval, used to interpolate the value at `start`. If `NULL`, the first timestamp in `summary` is used as the start of the interval.
`next`	`GaugeSummary`	The gauge aggregate from the next interval, used to interpolate the value at `start + interval`. If `NULL`, the last timestamp in `summary` is used as the end of the interval.

Returns

Column	Type	Description
`interpolated_rate`	`DOUBLE PRECISION`	The per-second rate of change of the gauge between the specified bounds. If exact values are missing in the raw data for the first and last points, these values are interpolated linearly from the neighboring gauge aggregates.

Examples

Calculate the per-second rate of change for each 15-minute interval, using interpolation to get the values at the interval boundaries if they don't exist in the data:


SELECT
    id,
    bucket,
    interpolated_rate(
        summary,
        bucket,
        '15 min',
        LAG(summary) OVER (PARTITION BY id ORDER by bucket),
        LEAD(summary) OVER (PARTITION BY id ORDER by bucket)
    )
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

irate_left(
  summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the instantaneous rate of change at the left, or earliest, edge of a gauge aggregate. This is equal to the second value minus the first value, divided by the time lapse between the two points. This calculation is useful for fast-moving gauges.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`idelta_left`	`DOUBLE PRECISION`	The instantaneous rate of change at the left, or earliest, edge of the gauge aggregate

Examples

Get the instantaneous rate of change at the start of each 15-minute gauge aggregate:


SELECT
    id,
    bucket,
    irate_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

irate_right(
  summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the instantaneous rate of change at the right, or latest, edge of a gauge aggregate. This is equal to the last value minus the second-last value, divided by the time lapse between the two points. This calculation is useful for fast-moving gauges.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`idelta_right`	`DOUBLE PRECISION`	The instantaneous rate of change at the right, or latest, edge of the gauge aggregate

Examples

Get the instantaneous rate of change at the end of each 15-minute gauge aggregate:


SELECT
    id,
    bucket,
    irate_right(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

num_changes(
    summary GaugeSummary
) RETURNS BIGINT

Get the number of times the gauge changed during the period summarized by the gauge aggregate.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge summary created using `gauge_agg`

Returns

Column	Type	Description
`num_changes`	`BIGINT`	The number of times the gauge changed

Examples

Get the number of times the gauge changed over each 15-minute interval:


SELECT
    id,
    bucket,
    num_changes(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

num_elements(
    summary GaugeSummary
) RETURNS BIGINT

Get the number of points with distinct timestamps from a gauge aggregate. Duplicate timestamps are ignored.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`num_elements`	`BIGINT`	The number of points with distinct timestamps

Examples

Get the number of points for each 15-minute gauge aggregate:


SELECT
    id,
    bucket,
    num_elements(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

rate(
    summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the rate of change of the gauge. This is the simple rate, equal to the last value minus the first value, divided by the time elapsed.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`rate`	`DOUBLE PRECISION`	The rate of change of the gauge

Examples

Get the rate of change per id over the entire recorded interval:


SELECT
    id,
    rate(summary)
FROM (
    SELECT
        id,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id
) t

slope(
    summary GaugeSummary
) RETURNS DOUBLE PRECISION

Calculate the slope of the linear least-squares fit for a gauge aggregate. The dependent variable is the gauge value, and the independent variable is time. Time is always in seconds, so the slope estimates the per-second rate of change. This gives a result similar to rate, but it can more accurately reflect the usual gauge behavior in the presence of infrequent, abnormally large changes.

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`slope`	`DOUBLE PRECISION`	The slope of the linear least-squares fit

Examples

Calculate the gauge slope per id and per 15-minute interval:


SELECT
    id,
    bucket,
    slope(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

time_delta(
    summary GaugeSummary
) RETURNS DOUBLE PRECISION

Get the number of seconds between the first and last measurements in a gauge aggregate

Required arguments

Name	Type	Description
`summary`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`time_delta`	`DOUBLE PRECISION`	The difference, in seconds, between the first and last times

Examples

Get the time difference between the first and last gauge readings for each 15-minute interval. Note this difference isn't necessarily equal to 15 minutes * 60 seconds / minute, because the first and last readings might not fall exactly on the interval boundaries:


SELECT
    id,
    bucket,
    time_delta(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

rollup(
    cs GaugeSummary
) RETURNS GaugeSummary

This function combines multiple gauge aggregates into one. This can be used to combine aggregates from adjacent intervals into one larger interval, such as rolling daily aggregates into a weekly or monthly aggregate.

Required arguments

Name	Type	Description
`cs`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`

Returns

Column	Type	Description
`gauge_agg`	`GaugeSummary`	A new gauge aggregate created by combining the input gauge aggregates

with_bounds(
    summary GaugeSummary,
    bounds TSTZRANGE,
) RETURNS GaugeSummary

Add time bounds to an already-computed gauge aggregate. Bounds are necessary to use extrapolation accessors on the aggregate.

Required arguments

Name	Type	Description
`cs`	`GaugeSummary`	A gauge aggregate created using `gauge_agg`
`bounds`	`TSTZRANGE`	A range of `timestamptz` giving the smallest and largest allowed times in the gauge aggregate

Returns

Column	Type	Description
`gauge_agg`	`GaugeSummary`	A new gauge aggregate with the bounds applied

Examples

Create a gauge aggregate for each id and each 15-minute interval. Then add bounds to the gauge aggregate, so you can calculate the extrapolated rate:


SELECT
    id,
    bucket,
    extrapolated_rate(
        with_bounds(
            summary,
            time_bucket_range('15 min'::interval, bucket)
        )
    )
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t

gauge_agg() functions

Related hyperfunction groups

Two-step aggregation

Functions in this group

Warning

Aggregate

Accessor

Rollup

Mutator

Function details

gauge_agg()

corr()

delta()

extrapolated_delta()

extrapolated_rate()

gauge_zero_time()

idelta_left()

idelta_right()

intercept()

interpolated_delta()

interpolated_rate()

irate_left()

irate_right()

num_changes()

num_elements()

rate()

slope()

time_delta()

rollup()

with_bounds()

gauge_agg() functions

Introduction

Related hyperfunction groups

Two-step aggregation

Functions in this group

Warning

Aggregate

Accessor

Rollup

Mutator

Function details

gauge_agg()

corr()

delta()

extrapolated_delta()

extrapolated_rate()

gauge_zero_time()

idelta_left()

idelta_right()

intercept()

interpolated_delta()

interpolated_rate()

irate_left()

irate_right()

num_changes()

num_elements()

rate()

slope()

time_delta()

rollup()

with_bounds()

Related Content