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.