Timescale counter_agg(double precision, double precision) does not exist

Question

I have two tables in my database:

• table1 contains a time series of t vs var1 for a group of 5 different sensors – each sensor has its own id value;
• table2 that contains a time series of t vs var2 for another group of id values

The time points of var1 and var2 are not identical, and they are sampled once per second-ish but at an arbitrary time.

I wrote a query that is attempting to:

1. take the last 10 seconds of data, and resample both var1 and var2 at a round number of seconds using time_bucket, and then;
2. correlate the two resampled var1 and var2 time series across the two tables.

Step 1 is done because I'm assuming it's needed to do step 2, but I might be wrong.

Here's the timescale sql query I've put together:

    WITH my_selection AS (
        SELECT
            time_bucket('1 seconds', a.t) as bucket,
            counter_agg(a.t_average, b.a_mag) as summary
        FROM table1 a
        LEFT JOIN table2 b
        ON time_bucket('1 seconds', a.t) = time_bucket('1 seconds', b.t)
        WHERE a.t > now () - INTERVAL '10 seconds'
        AND   (b.t > now () - INTERVAL '10 seconds'
        AND a.id = '1'
        AND b.id = '2')
    )
    SELECT
        bucket,
        corr(summary)
    FROM my_selection
    ORDER BY bucket

I'm getting the error: "function counter_agg(double precision, double precision) does not exist"

Toolkit is installed and I can run simpler counter_agg examples.

I'm new to sql and timescale, so I'm likely overcomplicating this or coming at it wrong.

Any ideas?

EDIT: For anyone else who finds this later, I made another mistake: corr(summary) is a single value so it makes sense to do something simpler like this:

WITH my_selection AS (
    SELECT
        stats_agg(a.t_average, b.a_mag) as summary
    FROM table1 a
    LEFT JOIN table2 b
    ON time_bucket('1 second', a.t) = time_bucket('1 second', b.t)
    WHERE a.t > now () - INTERVAL '10 seconds'
    AND   (b.t > now () - INTERVAL '10 seconds'
    AND a.id = '1'
    AND b.id = '2')
)
SELECT
    corr(summary)
FROM  my_selection

Answer 1

generally, I fall into this problem too. It's related to the function signature as Postgresql has several types of values.

You can use \df counter_agg to get to know the signature:

playground=# \df counter_agg
List of functions
┌─[ RECORD 1 ]────────┬───────────────────────────────────────────────────────────────────────┐
│ Schema              │ public                                                                │
│ Name                │ counter_agg                                                           │
│ Result data type    │ countersummary                                                        │
│ Argument data types │ ts timestamp with time zone, value double precision                   │
│ Type                │ agg                                                                   │
├─[ RECORD 2 ]────────┼───────────────────────────────────────────────────────────────────────┤
│ Schema              │ public                                                                │
│ Name                │ counter_agg                                                           │
│ Result data type    │ countersummary                                                        │
│ Argument data types │ ts timestamp with time zone, value double precision, bounds tstzrange │
│ Type                │ agg                                                                   │
└─────────────────────┴───────────────────────────────────────────────────────────────────────┘

As you can see, the function has a different signature.

As you're looking for the corr probably, you'll need to use stats_agg instead of counter_agg. Check the docs

WITH my_selection AS (
        SELECT
            time_bucket('1 seconds', a.t) as bucket,
            stats_agg(a.t_average, b.a_mag) as summary
        FROM table1 a
        LEFT JOIN table2 b
        ON time_bucket('1 seconds', a.t) = time_bucket('1 seconds', b.t)
        WHERE a.t > now () - INTERVAL '10 seconds'
        AND   (b.t > now () - INTERVAL '10 seconds'
        AND a.id = '1'
        AND b.id = '2')
    )
    SELECT
        bucket,
        corr(summary)
    FROM my_selection
    ORDER BY bucket