Handle unevenly sampled time series data

Use time-weighted averages and integrals with irregularly sampled time-series data

Time weighted averages and integrals are used when a time series is not evenly sampled. Time series data points are often evenly spaced, for example every 30 seconds, or every hour. But sometimes data points are recorded irregularly, for example if a value has a large change, or changes quickly. Computing an average using data that is not evenly sampled is not always useful.

For example, if you have a lot of ice cream in freezers, you need to make sure the ice cream stays within a 0-10℉ (-20 to -12℃) temperature range. The temperature in the freezer can vary if folks are opening and closing the door, but the ice cream only has a problem if the temperature is out of range for a long time. You can set your sensors in the freezer to sample every five minutes while the temperature is in range, and every 30 seconds while the temperature is out of range. If the results are generally stable, but with some quick moving transients, an average of all the data points weights the transient values too highly. A time weighted average weights each value by the duration over which it occurred based on the points around it, producing much more accurate results.

Time weighted integrals are useful when you need a time-weighted sum of irregularly sampled data. For example, if you bill your users based on irregularly sampled CPU usage, you need to find the total area under the graph of their CPU usage. You can use a time-weighted integral to find the total CPU-hours used by a user over a given time period.

Time weighted average in is implemented with the time_weight() function, which weights each value using last observation carried forward (LOCF), or linear interpolation. The aggregate is not parallelizable, but it is supported with continuous aggregation.

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

Calculate time-weighted averages

This example uses a freezer_temps table that simulates irregularly sampled temperature data from freezers. The sampling rate increases when temperatures go out of range, demonstrating the value of time-weighted averages.

1. Create the freezer_temps hypertable

   CREATE TABLE freezer_temps (
     ts TIMESTAMPTZ NOT NULL,
     freezer_id INTEGER,
     temperature DOUBLE PRECISION
   ) WITH (tsdb.hypertable);
   

2. Insert sample data with irregular sampling

   This simulates sensors that sample every 5 minutes when temperatures are in range (0-10°F), and more frequently when out of range:

   -- Insert in-range readings (every 5 minutes)
   INSERT INTO freezer_temps (ts, freezer_id, temperature)
   SELECT
     time,
     freezer_id,
     random() * 10 as temperature
   FROM generate_series(
     now() - interval '7 days',
     now(),
     interval '5 minutes'
   ) AS g1(time),
   generate_series(1, 3) AS g2(freezer_id);
   
   -- Insert some out-of-range readings (every 30 seconds during problems)
   INSERT INTO freezer_temps (ts, freezer_id, temperature)
   SELECT
     time,
     1 as freezer_id,
     15 + random() * 5 as temperature
   FROM generate_series(
     now() - interval '3 days',
     now() - interval '3 days' + interval '2 hours',
     interval '30 seconds'
   ) AS g1(time);
   

3. 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;
   

4. Check for irregular data

   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 how time-weighted averages work, read the time-weighted averages blog.