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.
In this procedure, we are using an example table called
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.
Found an issue on this page?Report an issue!