[Bug]: time_bucket_gapfill with named timezone gives incorrect result

[intermittentnrg]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Gapfill

What happened?

I host Grafana at https://intermittent.energy which has electricity grid data from around the world for many timezones like Europe/America/Australia.

Grafana 10.1.0 just added the variable $__timezone which has value like Europe/Stockholm which is correct value for time_bucket_gapfill timezone parameter https://docs.timescale.com/api/latest/hyperfunctions/gapfilling/time_bucket_gapfill#time_bucket_gapfill.

However it gives incorrect result:

Without timezone parameter I get expected result:

The only difference in these screenshots is the 'Europe/Stockholm' parameter to time_bucket_gapfill.

Direct link to edit query in Grafana screenshots above, which allows edit and run query as anonymous visitor: https://intermittent.energy/d/QCEg6rl7z/generation?orgId=1&var-region=europe&var-area_type=country&var-area=10&var-production_type=5&var-group_by=production_type&var-demand=0&var-min_interval=1h&var-gapfill_function=interpolate&from=now-1y%2Fy&to=now-1y%2Fy&editPanel=4

TimescaleDB version affected

2.12.2, 2.13.1, 2.14.2

PostgreSQL version used

14 (using timescaledb/timescaledb -pg14 docker image

What operating system did you use?

Docker on k3s, Ubuntu 22.04.3 LTS with containerd 1.7.2-0ubuntu1~22.04.1

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

Inspect data with time_bucket_gapfill(..., timezone) in Grafana shows these rows returned (click Query Insector > Data from above link, after editing timezone parameter):

2023-10-26 18:00:00 292 MW
2023-10-26 19:00:00 -19.1 GW
2023-10-27 00:00:00 256 MW
2023-10-27 01:00:00 -19.2 GW
2023-10-27 06:00:00 337 MW
2023-10-27 07:00:00 -19.2 GW
2023-10-27 12:00:00 375 MW

How can we reproduce the bug?

I suppose you want a reproducible dbfiddle aswell, but thought I'd report this early as it seems as a clear bug, and can be reproduced via links to my grafana per above.

[gayyappan]

@intermittentnrg Could you please provide the table definitions for the tables involved. It would help to have some sample rows from the hypertable to see if we can repro the problem ( Are any continuous aggregates involved in the query?)

Could you also please paste the query here: I tried to add it but not able to copy and paste.

[intermittentnrg]

Full query from query inspector:

SELECT
  time,
  CONCAT_WS('/', production_type)||  CASE WHEN negative THEN '_negative' ELSE '' END AS metric,
  SUM(value) AS value
FROM (
  SELECT
    time_bucket_gapfill('6h', time) AS time,
    a.code AS area,
    pt.name AS production_type,
    value<0 AS negative,
    interpolate(AVG(value)) AS value
  FROM generation g
  INNER JOIN areas a ON(area_id=a.id)
  INNER JOIN production_types pt ON(production_type_id=pt.id)
  WHERE
    time BETWEEN '2022-12-31T23:00:00Z' AND '2023-12-31T22:59:59.999Z' AND
    production_type_id IN('5') AND
    area_id IN('10')
  GROUP BY 1,2,3,value<0
) AS s
GROUP BY 1,production_type,negative
HAVING SUM(value) IS NOT NULL
ORDER BY 1

generation is actually a view that selects time,value with a join to get area_id and production_type_id

 SELECT apt.area_id,
    apt.production_type_id,
    g."time",
    g.value,
    g.areas_production_type_id,
    apt.source_area_id
   FROM generation_data g
     JOIN areas_production_types apt ON g.areas_production_type_id = apt.id;

postgres=# \d generation_data
                        Table "intermittency.generation_data"
          Column          |           Type           | Collation | Nullable | Default 
--------------------------+--------------------------+-----------+----------+---------
 value                    | integer                  |           | not null | 
 time                     | timestamp with time zone |           | not null | 
 areas_production_type_id | smallint                 |           | not null | 

I need to make a dbfiddle I guess..

[intermittentnrg]

The issue seems to start from 2023-03-31 when daylight savings happened

[intermittentnrg]

Created dbfiddle that reproduces: https://dbfiddle.uk/ltQgomQN

[intermittentnrg]

Here is a much shorter dbfiddle https://dbfiddle.uk/b8DUocAn

SELECT time_bucket_gapfill('7d', time, 'Europe/Stockholm') AS time,
       AVG(extract(epoch from time))
FROM generate_series('2024-03-14'::timestamptz, '2024-04-14'::timestamptz, '1d') AS time
WHERE time BETWEEN '2024-03-14' AND '2024-04-14'
GROUP BY 1

[intermittentnrg]

Another example that had me confused, cause it doesn't ORDER BY time even when I told it to: https://dbfiddle.uk/ySRKeqsp

Looks like it's gapfiling the previous DST timezone?

[intermittentnrg]

Ah sorry I didn't ALTER EXTENSION timescaledb UPDATE after upgrading docker image to TimescaleDB 2.14!

Seems this was bug #6507 time_bucket_gapfill with timezones doesn't handle daylight savings

There is #6510 backport to 2.13 merged but per changelog it's not been released.

[intermittentnrg]

This issue is resolved and ready to be closed.

Would be good if there was a new 2.13 release that includes the backported fix tho.

[antekresic]

I'm sorry, I don't think we offer that kind long term support for individual minor releases. Your best bet is to upgrade to 2.14.2.

TimescaleDB is under heavy development and we are actually in the process of releasing version 2.15.

I'll close this issue, feel free to reopen it if you have any other questions.

Thanks again!