Re: where clauses including timstamptz and intervals

Ron <ronljohnsonjr(at)gmail(dot)com> writes:
> On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote:
>> For example, in my timezone:
>>
>> $ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month'), '2020-11-01 00:00:00+00'::timestamptz;
>> ?column? │ timestamptz
>> ────────────────────────┼────────────────────────
>> 2020-11-01 02:00:00+01 │ 2020-11-01 01:00:00+01
>> (1 row)
>>
>> Please note that there is 1 hour difference.
>> The reason is that somewhere in there we change time due to daylight
>> savings.

> That looks like a flaw in how "month" is calculated.  Whether or not October
> is 744 hours (31 days x 24 hours/day) or 743 hours (subtracting the "fall
> back" hour), one month from 01-Oct-2020 is still 01-Nov-2020.

No, it's more likely because the calculation was done in some time
zone other than UTC, which (somewhat confusingly) is what the starting
value was specified in. Assuming Hubert meant EU rules, we have

regression=# set timezone = 'Europe/Paris';
SET
regression=# select '2020-10-01 00:00:00+00'::timestamptz;
timestamptz
------------------------
2020-10-01 02:00:00+02
(1 row)

regression=# select '2020-10-01 00:00:00+00'::timestamptz + interval '1 month';
?column?
------------------------
2020-11-01 02:00:00+01
(1 row)

which looks to me like the addition did exactly what it's supposed to,
ie same local time 1 month later.

regards, tom lane