Time-weighted average

Timescale's time weighted average is implemented as an aggregate that weights each value using last observation carried forward (LOCF), or linear interpolation. The aggregate is not parallelizable, but it is supported with continuous aggregation.

Run a time-weighted average query

In this procedure, we are using an example table called freezer_temps that contains data about internal freezer temperatures.

Running a time-weighted average query

At the psqlprompt, find the average and the time-weighted average of the data:


SELECT freezer_id,
  avg(temperature),
 average(time_weight('Linear', ts, temperature)) as time_weighted_average
FROM freezer_temps
GROUP BY freezer_id;

To determine if the freezer has been out of temperature range for more than 15 minutes at a time, use a time-weighted average in a window function:


SELECT *,
average(
        time_weight('Linear', ts, temperature) OVER (PARTITION BY freezer_id ORDER BY ts RANGE  '15 minutes'::interval PRECEDING )
       ) as rolling_twa
FROM freezer_temps
ORDER BY freezer_id, ts;

For more information about time-weighted average API calls, see the hyperfunction API documentation.

Time-weighted average

Run a time-weighted average query

Running a time-weighted average query

Related Content