Re: where clauses including timstamptz and intervals

On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote:
> On Fri, Apr 09, 2021 at 07:24:54AM +0000, Niels Jespersen wrote:
>> Hello all
>>
>> Are these two queries exactly eqivalent? The table is partitioned on
>> r_time, which is a timestamptz. The explain plans are not exactly the
>> same. The first wants to scan a partition more than the latter.
>>
>> select f.xx from f
>> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
>> and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month');
>>
>> select f.xx from f
>> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
>> and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz);
> It depends on timezone.
>
> 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.

--
Angular momentum makes the world go 'round.