interpolated_delta throws "unable to add final interpolated point: OrderError"

[blissi]

Relevant system information:

• OS: Ubuntu 22.04
• PostgreSQL version (output of SELECT version();): 16.4
• TimescaleDB Toolkit version (output of \dx timescaledb_toolkit in psql): 1.18.0
• Installation method: docker

Describe the bug
I get an error "unable to add final interpolated point: OrderError" when I use interpolated_delta() with a time zone passed to time_bucket. Without a time zone, it works.

To Reproduce
Steps to reproduce the behavior:

1. Create a new hypertable and populate it:

CREATE TABLE test
(
    ts    TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    value DOUBLE PRECISION
);


SELECT create_hypertable('test', by_range('ts'));


INSERT INTO test(ts, value)
VALUES ('2023-10-29 05:26:45.411000 +00:00', 25176.6),
       ('2023-10-29 22:56:45.922000 +00:00', 25196  ),
       ('2023-10-30 05:27:46.112000 +00:00', 25201.5),
       ('2023-10-30 20:07:41.113900 +00:00', 25234  );

2. Run the following query:

WITH bucketed AS (SELECT time_bucket('1 day', ts, 'Europe/Berlin') AS bucket,
                         counter_agg(ts, value)                    AS cntr
                  FROM test
                  GROUP BY bucket
                  ORDER BY bucket)
SELECT bucket,
       interpolated_delta(
               cntr,
               bucket,
               '1 day'::INTERVAL,
               LAG(cntr) OVER ordered_meter,
               LEAD(cntr) OVER ordered_meter)
FROM bucketed
WINDOW ordered_meter AS (ORDER BY bucket);

3. An error occurs: "unable to add final interpolated point: OrderError"

Expected behavior
The query should work.

Actual behavior
It shows the error I mentioned above.

Additional context
When I don't pass a time zone to the time_bucket, there is no error. So it may be related to that.

[GiuseppeP96]

I'm having the same issue! 😪

[arutz]

I am facing the same issue with one of our features. What I could specifically make out in may example is that this seems to be related to daylight saving transitions coupled with the use of timezone.

Looking at your test data there is a transition for Europe (For example "Europe/Berlin") right around the time of your sample data as well (At 2023-10-29T03:00+02:00 to +01:00).

[dev-francisco-bernardi]

Having exactly the same issue here. Any update on when would this be fixed or if there's a workaround? We have continuous aggregations using a specific timezone, but we can not use interpolated_delta over them 😢