Counter aggregates

Accounting for resets can be difficult to do in SQL, so Timescale has developed aggregate and accessor functions that handle calculations for counters in a more practical way.

note

Counter aggregates can be used in continuous aggregates, even though they are not parallelizable in PostgreSQL. For more information, see the section on parallelism and ordering.

In this procedure, we are using an example table called that contains counter data.

1. Create a table called example:

2. SELECT measure_id,
       delta(
           counter_agg(ts, val)
   FROM example
   GROUP BY measure_id;

3. You can also use the time_bucket function to produce a series of deltas over fifteen minute increments:

If your series is less regular, the deltas are affected by the number of samples in each fifteen minute period. You can improve this by using the extrapolated_delta function. To do this, you need to provide bounds that define where to extrapolate to. In this example, we use the time_bucket_range function, which works in the same way as but produces an open ended range of all the times in the bucket. This example also uses a CTE to do the counter aggregation, which makes it a little easier to understand what’s going on in each part.

1. Create a table called example:

   CREATE TABLE example (
       measure_id      BIGINT,
       ts              TIMESTAMPTZ ,
       val             DOUBLE PRECISION,
       PRIMARY KEY (measure_id, ts)
   );

note

Your counter aggregate might be more useful if you make a continuous aggregate out of it.

1. Create a hypertable partitioned on the column:

   SELECT create_hypertable('example', 'ts', chunk_time_interval=> '15 days'::interval, migrate_data => true);

2. Create the continuous aggregate:

3. You can also re-aggregate from the continuous aggregate into a larger bucket size:

   SELECT
       measure_id,
       time_bucket('1 day'::interval, bucket),
       delta(
           rollup(counter_agg)
       )
   FROM example_15
   

For more information about parallelism and ordering, see our