Here we have original data for 30 minute of interval and some of the case we will have random data within a day. When we are trying to aggregate data for day using the interpolated delta function. We have attached the sheet of original data and aggregated data.
As per documentation value should be calculated using the formula:
Interpolated value=y1?+((t−t1)/(t2-t1)??)×(y2?−y1?)
But the value is not matching with this formula.
Our use case scenario is, We have energy meters that has cumulative type of channel and send random data and in dashboard we want to show the usage for that day.
Interpolated Data.csv
Original Data.csv
We have used below queries to calculate the interpolated value.
WITH daily_meter AS (
SELECT
meter_id,
time_bucket (‘1 day’, md.“offset_timestamp”) AS bucket,
counter_agg (md.“offset_timestamp”, md.value)
FROM
meter_data md
WHERE
md.meter_channel_id = 289106571
AND lower(md.meter_id) = lower(‘Avalon_502-SP’)
AND md.“offset_timestamp” >= (to_timestamp(‘2024-04-01 00:00’, ‘YYYY-MM-DD hh24:mi:ss’)::timestamp + INTERVAL’-1 millisecond’)
AND md.“offset_timestamp” <= (to_timestamp(‘2024-04-05 00:00’, ‘YYYY-MM-DD hh24:mi:ss’)::timestamp + INTERVAL’-1 millisecond’)
GROUP BY
1,
2
)
SELECT
bucket,
meter_id,
first_val (counter_agg),
last_val (counter_agg),
interpolated_delta (counter_agg, bucket, ‘1 day’::interval, lag(counter_agg) OVER ordered_meter, lead(counter_agg) OVER ordered_meter)
FROM
daily_meter
WINDOW ordered_meter AS (PARTITION BY meter_id ORDER BY bucket)
ORDER BY
meter_id,
bucket;