Is there a way to extract start and end from a TimeWeightSummary - Timescaledb

Viewed 26

I'm using the time_weight function from timescaledb in a SQL Query. From the doc time_weight is defined as:

An aggregate that produces a TimeWeightSummary from timestamps and associated values.

Also in the doc we can read

Internally, the first and last points seen as well as the calculated weighted sum are stored in each TimeWeightSummary

Is there a way to extract the first and the last point value and timestamp from this TimeWeightSummary ?

Here is my SQL query:

WITH t as (
    SELECT
        time_bucket('1 hour'::interval, time) as dt,
        time_weight('Linear', time, p) AS tw
    FROM tsdb.asset_sensor_sample
    WHERE asset = 16
    GROUP BY time_bucket('1 hour'::interval, time)
)
SELECT
    dt,
    tw,   
    average(tw) -- extract the average from the time weight summary
FROM t LIMIT 5;

Here is the result:

           dt           |                                                                                              tw                                                                                              |      average       
------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
 2022-06-20 10:00:00+00 | (version:1,first:(ts:"2022-06-20 10:15:43.274976+00",val:127334.29194694175),last:(ts:"2022-06-20 10:55:43.274976+00",val:54155.74946698491),weighted_sum:239731880571173.1,method:Linear)   |  99888.28357132213
 2022-06-20 11:00:00+00 | (version:1,first:(ts:"2022-06-20 11:05:43.274976+00",val:72054.79620663091),last:(ts:"2022-06-20 11:55:43.274976+00",val:117667.04302813657),weighted_sum:247386550516233.63,method:Linear)  |   82462.1835054112
 2022-06-20 12:00:00+00 | (version:1,first:(ts:"2022-06-20 12:05:43.274976+00",val:95982.76112628987),last:(ts:"2022-06-20 12:55:43.274976+00",val:83790.58995691259),weighted_sum:259849005747598.72,method:Linear)   |  86616.33524919958
 2022-06-20 13:00:00+00 | (version:1,first:(ts:"2022-06-20 13:05:43.274976+00",val:108062.03874288549),last:(ts:"2022-06-20 13:55:43.274976+00",val:117030.17726887773),weighted_sum:329446374963518.94,method:Linear) | 109815.45832117298
 2022-06-20 14:00:00+00 | (version:1,first:(ts:"2022-06-20 14:05:43.274976+00",val:64564.42379745973),last:(ts:"2022-06-20 14:55:43.274976+00",val:95290.48787317045),weighted_sum:303986848836652.75,method:Linear)   | 101328.94961221758
(5 rows)

We can clearly see that these informations are available in the tw column. But I don't know to extract it.

0 Answers
Related