interpolated_delta calculation

[VishalSolankiTatvaSoft]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Other

What happened?

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;

TimescaleDB version affected

2.17

PostgreSQL version used

15.10

What operating system did you use?

Windows 11

What installation method did you use?

Other

What platform did you run on?

Managed Service for TimescaleDB (MST/Aiven)

Relevant log output and stack trace

No response

How can we reproduce the bug?

We have used the 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;

[kpan2034]

@VishalSolankiTatvaSoft I ran the query you provided on the original data, and got a different result than the one you got:

         bucket         |   meter_id    | first_val | last_val | interpolated_delta
------------------------+---------------+-----------+----------+--------------------
 2024-04-01 00:00:00+00 | Avalon_502-SP |    493.24 |   505.39 | 12.150000016666638
 2024-04-02 00:00:00+00 | Avalon_502-SP |    505.42 |   527.48 | 22.090000011111158
 2024-04-03 00:00:00+00 | Avalon_502-SP |    527.53 |   544.69 | 17.210000494444444
 2024-04-04 00:00:00+00 | Avalon_502-SP |    545.63 |   561.14 | 16.449999477777737
(4 rows)

(END)

• There's no data for the bucket 2024-03-31
• The interpolated_delta for 2024-04-01 is 12.150000016666638, instead of 12.209999983333319, which is what you'd expect.

This is definitely odd. Can you share the details of the meter_data table?

[VishalSolankiTatvaSoft]

meter_data

meter_data schema and data.txt

I have attached meter_data schema and data script. Also, In my system, I have set UTC date & time.

[kpan2034]

@VishalSolankiTatvaSoft I used these details and got the same output as before. When I change my timezone to something else (e.g. MST), the output changes, but the result is what you'd expect due to interpolation at the end points:

         bucket         |   meter_id    | first_val | last_val | interpolated_delta
------------------------+---------------+-----------+----------+--------------------
 2024-03-31 17:00:00-07 | Avalon_502-SP |    493.24 |   503.63 | 10.390000027777774
 2024-04-01 17:00:00-07 | Avalon_502-SP |    503.68 |   524.98 | 21.350000016666627
 2024-04-02 17:00:00-07 | Avalon_502-SP |    525.06 |   534.62 |  9.639999988888917
 2024-04-03 17:00:00-07 | Avalon_502-SP |    534.68 |   560.22 | 25.599999994444488
 2024-04-04 17:00:00-07 | Avalon_502-SP |    560.27 |   561.14 | 0.9199999722221719
(5 rows)

[VishalSolankiTatvaSoft]

@kpan2034 But, y1?+((t−t1)/(t2-t1)??)×(y2?−y1?) formula is used for calculate Interpolated delta value. When we calculate the Interpolated delta using the interpolated_delta function, that value does not match the formula. Or any other formula used for calculate Interpolated delta?

[kpan2034]

@VishalSolankiTatvaSoft
Interpolation happens at the end points, for e.g., the value at 2024-04-02 00:00:00.000 would be estimated using neighboring values present in the table.

We can try to verify this by including first_time and last_time in your data and calculating the expected value manually:

...
SELECT
    bucket,
    meter_id,
    first_val (counter_agg),
    last_val (counter_agg),
    first_time(counter_agg),
    last_time(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;

[VishalSolankiTatvaSoft]

@kpan2034 Thank you!! Do you have any formula for calculating interpolated_delta? I want to calculate manually using a formula.

I have tried to calculate using y1?+((t−t1)/(t2-t1)??)×(y2?−y1?) formula But the delta value didn't match with the function value.

[kpan2034]

@VishalSolankiTatvaSoft Yes, that formula should be correct. Just to note, this doesn't calculate delta directly; it calculates the end points of the intervals using linear interpolation and then uses those to calculate delta.