> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Handle unevenly sampled time series data

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

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

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][hyperfunctions-api-timeweight]
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 {TIMESCALE_DB} is implemented with the [`time_weight()`][hyperfunctions-api-timeweight]
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][caggs].

## Prerequisites

To follow the steps on this page:

* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

## 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**

   ```sql theme={"dark"}
   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:

   ```sql theme={"dark"}
   -- 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**

   ```sql theme={"dark"}
   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][hyperfunctions-api-timeweight] in a window function:

   ```sql theme={"dark"}
   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][blog-timeweight].

[blog-timeweight]: https://www.tigerdata.com/blog/what-time-weighted-averages-are-and-why-you-should-care

[caggs]: /manage-data/capabilities/continuous-aggregates/understand-continuous-aggregates

[hyperfunctions-api-timeweight]: /api-reference/timescaledb/hyperfunctions/time-weighted-calculations/time_weight