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:
Set the PostgreSQL session timezone to Europe/Berlin.
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.
Relevant system information:
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:
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;