[Bug]: time_bucket_gapfill with named timezone gives incorrect result

[intermittentnrg]

[Bug]: time_bucket_gapfill with named timezone gives incorrect result

[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!