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).