Interpolated_Average Error with Multi-Day Buckets Under Daylight Savings Time Settings in Europe/Berlin Timezone

[Timsgmlr]

Relevant system information:

• OS: Ubuntu 22.04.04 LTS
• PostgreSQL version (PostgreSQL 15.6 (Ubuntu 15.6-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
• TimescaleDB Toolkit version 1.18.0
• Installation method: docker

Describe the bug The query involving time_bucket_gapfill and interpolated_average fails when using a two (or more)-day bucket interval during the daylight saving period with the PostgreSQL session's timezone set to Europe/Berlin. It functions correctly when the timezone is set to UTC or CET, and interestingly, it also works without issue when using a one-day bucket interval under the same conditions.

To Reproduce Steps to reproduce the behavior:

1. Set the PostgreSQL session timezone to Europe/Berlin.
2. Execute the following SQL query:

SET TIMEZONE = 'EUROPE/BERLIN';

WITH generated_series AS (SELECT generate_series(
                                         TIMESTAMP WITH TIME ZONE '2024-03-27 00:00:00',
                                         TIMESTAMP WITH TIME ZONE'2024-04-08 23:59:59',
                                         INTERVAL '30 second'
                                 )               AS time,
                                 random() * 1000 AS raw_value),
     timeweighted_cte AS (SELECT time_bucket_gapfill(INTERVAL '2 days', time) AS timestamp,
                                 time_weight('locf', time, raw_value)         AS time_weight_summary,
                                 locf(last(raw_value, time)::NUMERIC)         AS value
                          FROM generated_series
                          WHERE time BETWEEN TIMESTAMP WITH TIME ZONE '2024-04-01 08:36:00+00:00'
                                    AND TIMESTAMP WITH TIME ZONE '2024-04-04 22:00:00+00:00'
                          GROUP BY timestamp)
SELECT time_bucket_gapfill(INTERVAL '2 days', timestamp) AS timestamp,
       interpolated_average(
               tws := time_weight_summary,
               start := timestamp,
               duration := INTERVAL '2 days')            AS value
FROM timeweighted_cte
ORDER BY timestamp;

3. Observe the error message: ERROR: Interval end (765327600000000) must be after last timestamp (765331170000000).

Expected behavior The query should execute without errors, regardless of the bucket interval or the timezone setting, including during periods that observe daylight saving time.

Actual behavior The query fails with an error message when a two-day bucket interval is used during the daylight saving period in the Europe/Berlin timezone. The issue does not occur when using a one-day bucket interval.