Re: where clauses including timstamptz and intervals

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.

If I'd set timezone to UTC, suddently it's the same:

$ set timezone=UTC;
SET

$ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month') = '2020-11-01 00:00:00+00'::timestamptz;
 ?column? 
──────────
 t
(1 row)

As usual - time is more complicated than one could expect.

Best regards,

depesz