[Bug]: time_bucket_gapfill using timezone and milliseconds returns incorrect data when DST occurs

[canegru]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Gapfill, Query executor

What happened?

It seems related to these issues that were fixed in the past. #6788 and #6908

In the query, if I specify the time as 2592000000 milliseconds, I get back null values without DST and the actual values with DST applied.

TimescaleDB version affected

2.16.1

PostgreSQL version used

15.8

What operating system did you use?

timescale cloud managed

What installation method did you use?

Not applicable

What platform did you run on?

Timescale Cloud

Relevant log output and stack trace

No response

How can we reproduce the bug?

SELECT time_bucket_gapfill(CONCAT('2592000000', ' ', 'milliseconds')::INTERVAL, time, timezone := 'America/New_York')
    AS timestamp
     , round(MAX("numericalValue")::NUMERIC, 2)
    AS aggregation_value

FROM data
WHERE "storageId" = 2188
  AND time BETWEEN '2024-01-01 05:00:00' AND '2024-09-22 03:09:24'
GROUP BY timestamp
ORDER BY timestamp;

returns

timestamp	aggregation_value
2023-12-28 05:00:00.000000 +00:00	3933
2024-01-27 05:00:00.000000 +00:00	4254
2024-02-26 05:00:00.000000 +00:00	3942
2024-03-27 05:00:00.000000 +00:00	null
2024-04-26 05:00:00.000000 +00:00	null
2024-05-26 05:00:00.000000 +00:00	null
2024-06-25 05:00:00.000000 +00:00	null
2024-07-25 05:00:00.000000 +00:00	null
2024-08-24 05:00:00.000000 +00:00	null
2024-03-27 04:00:00.000000 +00:00	3845
2024-04-26 04:00:00.000000 +00:00	6897
2024-05-26 04:00:00.000000 +00:00	4513
2024-06-25 04:00:00.000000 +00:00	6880
2024-07-25 04:00:00.000000 +00:00	6640
2024-08-24 04:00:00.000000 +00:00	7238

and specifying time as 30 days returns without nulls

SELECT time_bucket_gapfill(CONCAT('30', ' ', 'days')::INTERVAL, time, timezone := 'America/New_York')
    AS timestamp
     , round(MAX("numericalValue")::NUMERIC, 2)
    AS aggregation_value

FROM data
WHERE "storageId" = 2188
  AND time BETWEEN '2024-01-01 05:00:00' AND '2024-09-22 03:09:24'
GROUP BY timestamp
ORDER BY timestamp;

timestamp	aggregation_value
2023-12-28 05:00:00.000000 +00:00	3933
2024-01-27 05:00:00.000000 +00:00	4254
2024-02-26 05:00:00.000000 +00:00	3942
2024-03-27 04:00:00.000000 +00:00	3845
2024-04-26 04:00:00.000000 +00:00	6897
2024-05-26 04:00:00.000000 +00:00	4513
2024-06-25 04:00:00.000000 +00:00	6880
2024-07-25 04:00:00.000000 +00:00	6640
2024-08-24 04:00:00.000000 +00:00	7238

if I pass without timezone, data returns correctly.

SELECT time_bucket_gapfill(CONCAT('2592000000', ' ', 'milliseconds')::INTERVAL, time)
    AS timestamp
     , round(MAX("numericalValue")::NUMERIC, 2)
    AS aggregation_value

FROM data
WHERE "storageId" = 2188
  AND time BETWEEN '2024-01-01 05:00:00' AND '2024-09-22 03:09:24'
GROUP BY timestamp
ORDER BY timestamp;

timestamp	aggregation_value
2023-12-28 00:00:00.000000 +00:00	3933
2024-01-27 00:00:00.000000 +00:00	4254
2024-02-26 00:00:00.000000 +00:00	3942
2024-03-27 00:00:00.000000 +00:00	3845
2024-04-26 00:00:00.000000 +00:00	6897
2024-05-26 00:00:00.000000 +00:00	4513
2024-06-25 00:00:00.000000 +00:00	6880
2024-07-25 00:00:00.000000 +00:00	6640
2024-08-24 00:00:00.000000 +00:00	7238