Analyze intermittent time-series data

Analyze intermittent or irregular time-series data with heartbeat aggregation in TimescaleDB

Given a series of timestamped health checks, it can be tricky to determine the overall health of a system over a given interval. provides window functions that you use to get a sense of where unhealthy gaps are, but they can be somewhat awkward to use efficiently.

This is one of the many cases where provide an efficient, simple solution for a frequently occurring problem. Heartbeat aggregation helps analyze event-based time-series data with intermittent or irregular signals.

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 .

• This example uses the SustData public dataset.

  This dataset tracks the power usage of a small number of apartments and houses over four different deployment intervals. The data is collected in one-minute samples from each unit.

Create a materialized view with heartbeat aggregates

When you have loaded the data into hypertables, you can create a materialized view containing weekly heartbeat aggregates for each of the units.

1. Create the materialized view

   heartbeat_agg takes the following parameters: the timestamp column, the start of the interval, the length of the interval, and how long the aggregate is considered live after each timestamp. This example uses 2 minutes as the heartbeat lifetime to give some tolerance for small gaps.

   CREATE MATERIALIZED VIEW weekly_heartbeat AS
     SELECT
       time_bucket('1 week', tmstp) as week,
       iid as unit,
       deploy,
       heartbeat_agg(tmstp, time_bucket('1w', tmstp), '1w', '2m')
     FROM power_samples
     GROUP BY 1,2,3;
   

2. Query heartbeat data for a particular unit

   You can use this data to see when you’re receiving data for a particular unit. This example rolls up the weekly aggregates into a single aggregate using rollup(), and then views the live ranges using live_ranges():

   SELECT live_ranges(rollup(heartbeat_agg)) FROM weekly_heartbeat WHERE unit = 17;
   
                        live_ranges
   -----------------------------------------------------
    ("2010-09-18 00:00:00+00","2011-03-27 01:01:50+00")
    ("2011-03-27 03:00:52+00","2011-07-03 00:01:00+00")
    ("2011-07-05 00:00:00+00","2011-08-21 00:01:00+00")
    ("2011-08-22 00:00:00+00","2011-08-25 00:01:00+00")
    ("2011-08-27 00:00:00+00","2011-09-06 00:01:00+00")
    ("2011-09-08 00:00:00+00","2011-09-29 00:01:00+00")
    ("2011-09-30 00:00:00+00","2011-10-04 00:01:00+00")
    ("2011-10-05 00:00:00+00","2011-10-17 00:01:00+00")
    ("2011-10-19 00:00:00+00","2011-11-09 00:01:00+00")
    ("2011-11-10 00:00:00+00","2011-11-14 00:01:00+00")
    ("2011-11-15 00:00:00+00","2011-11-18 00:01:00+00")
    ("2011-11-20 00:00:00+00","2011-11-23 00:01:00+00")
    ("2011-11-24 00:00:00+00","2011-12-01 00:01:00+00")
    ("2011-12-02 00:00:00+00","2011-12-12 00:01:00+00")
    ("2011-12-13 00:00:00+00","2012-01-12 00:01:00+00")
    ("2012-01-13 00:00:00+00","2012-02-03 00:01:00+00")
    ("2012-02-04 00:00:00+00","2012-02-10 00:01:00+00")
    ("2012-02-11 00:00:00+00","2012-03-25 01:01:50+00")
    ("2012-03-25 03:00:51+00","2012-04-11 00:01:00+00")
   

3. Construct more elaborate queries

   To return the 5 units with the lowest uptime during the third deployment:

   SELECT unit, uptime(rollup(heartbeat_agg))
   FROM weekly_heartbeat
   WHERE deploy = 3
   GROUP BY unit
   ORDER BY uptime LIMIT 5;
   
    unit |      uptime
   ------+-------------------
      31 | 203 days 22:05:00
      34 | 222 days 22:05:00
      32 | 222 days 22:05:00
      35 | 222 days 22:05:00
      30 | 222 days 22:05:00
   

4. Combine aggregates from different units to get the combined coverage

   This example queries the interval where any part of a deployment was active:

   SELECT deploy, live_ranges(rollup(heartbeat_agg))
   FROM weekly_heartbeat group by deploy order by deploy;
   
    deploy |                     live_ranges
   --------+-----------------------------------------------------
         1 | ("2010-07-29 00:00:00+00","2010-11-26 00:01:00+00")
         2 | ("2010-11-25 00:00:00+00","2011-03-27 01:01:59+00")
         2 | ("2011-03-27 03:00:00+00","2012-03-25 01:01:59+00")
         2 | ("2012-03-25 03:00:26+00","2012-04-17 00:01:00+00")
         2 | ("2012-04-20 00:00:00+00","2012-04-21 00:01:00+00")
         2 | ("2012-05-11 00:00:00+00","2012-05-13 00:01:00+00")
         2 | ("2013-02-20 00:00:00+00","2013-02-21 00:01:00+00")
         3 | ("2012-08-01 00:00:01+00","2013-03-31 01:01:16+00")
         3 | ("2013-03-31 03:00:03+00","2013-05-22 00:01:00+00")
         4 | ("2013-07-31 00:00:00+00","2014-03-30 01:01:49+00")
         4 | ("2014-03-30 03:00:01+00","2014-04-25 00:01:00+00")
   

   You can use this data to make observations and draw conclusions:

   • The second deployment had a lot more problems than the other ones.
   • There were some readings from February 2013 that were incorrectly categorized as a second deployment.
   • The timestamps are given in a local time without time zone, resulting in some missing hours around springtime daylight savings time changes.