[Bug]: Wrong time offset value for timezone

[michalk-k]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Gapfill

What happened?

time_bucket_gapfill() function provides 3rd argument, the timezone.
Granted, in documentation, the timezone is explained only as a literal symbol, for example, "Europe/Prague".
Timezone can be expressed by timezone code and offset from GMT. For example "Europe/Prague" can be represented by +01:00 (or CET) or by +02:00 for summertime (CEST)

Note that + sign in the offset.

time_bucket_gapfill() accepts all three conventions (Europe/Prague, CET, +01:00). For Europe/Prague and CET returns the same values. When using the time offset it expects a negative sign (-1:00) in order to return correct values.

Here is an example. Note all dates are from winter time, matching CET or +1h offset to GMT.

WITH
src AS MATERIALIZED
(
    SELECT
        column1::TIMESTAMPTZ AS time,
        column2::NUMERIC AS val
    FROM
    (
        VALUES
        ('2025-01-27 01:00:12+01', 3737.7),
        ('2025-01-27 02:00:12+01', 3737.9),
        ('2025-01-27 03:00:12+01', 3738.1),
        ('2025-01-27 04:00:12+01', 3738.2),
        ('2025-01-27 05:00:12+01', 3738.4),
        ('2025-01-27 06:00:12+01', 3738.6),
        ('2025-01-27 07:00:13+01', 3738.9),
        ('2025-01-27 08:00:12+01', 3739.2),
        ('2025-01-27 09:00:12+01', 3739.4),
        ('2025-01-27 10:00:12+01', 3739.4),
        ('2025-01-27 11:00:12+01', 3739.4),
        ('2025-01-27 12:00:12+01', 3739.5),
        ('2025-01-27 13:00:12+01', 3739.5),
        ('2025-01-27 14:00:12+01', 3739.5),
        ('2025-01-27 15:00:12+01', 3739.6),
        ('2025-01-27 16:00:12+01', 3739.7),
        ('2025-01-27 17:00:12+01', 3739.7),
        ('2025-01-27 18:00:12+01', 3739.8),
        ('2025-01-27 19:00:12+01', 3739.8),
        ('2025-01-27 20:00:12+01', 3741.3),
        ('2025-01-27 21:00:12+01', 3741.9),
        ('2025-01-27 22:00:12+01', 3742.9),
        ('2025-01-27 23:00:12+01', 3743.5),
        ('2025-01-28 00:00:12+01', 3743.7),
        ('2025-01-28 01:00:12+01', 3743.9),
        ('2025-01-28 02:00:12+01', 3744.1),
        ('2025-01-28 03:00:12+01', 3744.3),
        ('2025-01-28 04:00:12+01', 3744.5),
        ('2025-01-28 05:00:12+01', 3744.6),
        ('2025-01-28 06:00:12+01', 3744.8),
        ('2025-01-28 07:00:12+01', 3745.1),
        ('2025-01-28 08:00:12+01', 3745.7),
        ('2025-01-28 09:00:12+01', 3746.0),
        ('2025-01-28 10:00:12+01', 3748.3),
        ('2025-01-28 11:00:13+01', 3748.7),
        ('2025-01-28 12:00:13+01', 3749.3),
        ('2025-01-28 13:00:13+01', 3749.4),
        ('2025-01-28 14:00:12+01', 3749.7),
        ('2025-01-28 15:00:13+01', 3750.1),
        ('2025-01-28 16:00:14+01', 3750.3),
        ('2025-01-28 17:00:12+01', 3750.6),
        ('2025-01-28 18:00:12+01', 3750.9),
        ('2025-01-28 19:00:13+01', 3751.9),
        ('2025-01-28 20:00:13+01', 3752.9),
        ('2025-01-28 21:00:13+01', 3753.4),
        ('2025-01-28 22:00:12+01', 3754.2),
        ('2025-01-28 23:00:12+01', 3754.5),
        ('2025-01-29 00:00:13+01', 3754.8),
        ('2025-01-29 01:00:12+01', 3755.1),
        ('2025-01-29 02:00:12+01', 3755.4),
        ('2025-01-29 03:00:12+01', 3755.7),
        ('2025-01-29 04:00:13+01', 3755.9),
        ('2025-01-29 05:00:12+01', 3756.0),
        ('2025-01-29 06:00:12+01', 3756.2),
        ('2025-01-29 07:00:12+01', 3756.7),
        ('2025-01-29 08:00:12+01', 3756.9),
        ('2025-01-29 09:00:12+01', 3757.0),
        ('2025-01-29 10:00:12+01', 3757.1),
        ('2025-01-29 11:00:12+01', 3757.1),
        ('2025-01-29 12:00:13+01', 3757.2),
        ('2025-01-29 13:00:12+01', 3757.2),
        ('2025-01-29 14:00:14+01', 3757.2),
        ('2025-01-29 15:00:12+01', 3757.2),
        ('2025-01-29 16:00:14+01', 3757.3),
        ('2025-01-29 17:00:12+01', 3757.4),
        ('2025-01-29 18:00:13+01', 3757.5),
        ('2025-01-29 19:00:13+01', 3757.5),
        ('2025-01-29 20:00:12+01', 3757.5),
        ('2025-01-29 21:00:13+01', 3757.6),
        ('2025-01-29 22:00:12+01', 3757.6),
        ('2025-01-29 23:00:12+01', 3757.6),
        ('2025-01-30 00:00:13+01', 3757.6)
    )
)
SELECT
    time_bucket_gapfill
    (
        '1d'::interval,      
        "time", 
        'Europe/Prague'
    ) AS timeb,
    delta(counter_agg(time, val)) AS value
FROM src
WHERE time BETWEEN '2025-01-27 0:00:00+01' AND '2025-01-30 0:00:00+01'
GROUP BY timeb

The result is:

         timeb          |       value        
------------------------+--------------------
 2025-01-26 23:00:00+00 |  5.800000000000182
 2025-01-27 23:00:00+00 | 10.800000000000182
 2025-01-28 23:00:00+00 |  2.799999999999727
 2025-01-29 23:00:00+00 |                   

The same result can be achieved replacing Europe/Prague with CET.
But with +01:01 the result is:

         timeb          |       value       
------------------------+-------------------
 2025-01-26 01:00:00+00 |                 0
 2025-01-27 01:00:00+00 |                 6
 2025-01-28 01:00:00+00 |                11
 2025-01-29 01:00:00+00 | 2.199999999999818

The same result as Europe/Prague can be achieved with offset -01:01 which is an incorrect representation of the CET timezone.

TimescaleDB version affected

2.18.2

PostgreSQL version used

17.3

What operating system did you use?

Fedora 41

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

See description