SQL Query Fails with 'OrderError' When Using '1 month' Time Buckets in Desired Timezone

Question

I have encountered an issue while trying to adjust a SQL query to use time buckets in a specific timezone.

I initially used an example query from the timescale forum to solve a similar problem, and it worked perfectly fine.

As i changed the query to create time-buckets in a specific timezone, i run into an error. For time-bucket sizes like ["1 hour", "1 day", "1 week", "1 year"] it works perfect.

However, when I attempt to use "1 month" as the bucket size, the query fails and returns the error: "unable to add final interpolated point: OrderError".

My failing query looks like this:

 WITH bucketed AS (
        SELECT 
            measurement_id, 
            time_bucket('1 month', time, 'Europe/Zurich') as bucket,   
            counter_agg(time, value) AS counter
        FROM measurement_counter
        WHERE time BETWEEN '2021-01-01 00:00:00+0200' AND '2022-06-01 00:00:00+0200'
        GROUP BY measurement_id, bucket
        ORDER BY measurement_id, bucket)
    
    SELECT 
        bucket, 
        measurement_id, 
        interpolated_delta(
            counter,  bucket,  '1 month'::interval, 
            lag(counter) OVER ordered_measurement, 
            lead(counter) OVER ordered_measurement) AS delta_value
    FROM bucketed
    WINDOW ordered_measurement AS (PARTITION BY measurement_id ORDER BY bucket)
    ORDER BY measurement_id, bucket;

My hypertable was created like this:

`CREATE TABLE measurement_counter( time TIMESTAMPTZ NOT NULL, measurement_id BIGINT NOT NULL, value DOUBLE PRECISION NULL );

SELECT create_hypertable('measurement_counter', by_range('time'));`

I filled the hypertable with 3 years worth of data to test it out. Initially, I filled it with 15-minute intervals ('2020-01-01 00:00:00', '2020-01-01 00:15:00', etc.). Then, I changed it to 1-hour intervals ('2020-01-01 00:00:00', '2020-01-01 01:00:00', etc.), as this will be the use case in my database. As expected, this change had no effect.

Next, I recreated the table and adjusted the chunk size from the default (7 days) to 1 day and later to 1 month, but it still did not work.

It doesn't matter what time-frame i choose, if both start and end are inside the data that exists or one / both are outside of the scope.

All these different settings worked perfectly when I did not convert the buckets to another time zone. I am unable to set the whole table to one specific time zone as it needs to be variable.