Re: where clauses including timstamptz and intervals - Mailing list pgsql-general

Ron:

On Fri, Apr 9, 2021 at 5:53 PM Ron <ronljohnsonjr@gmail.com> wrote:
> >> 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.
....
> 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.

I've encountered these thing before. What is probably happening is
that your CLIENT TIMEZONE is DST aware.

When you add the interval 1st PG takes the tstz, and converts it to
internal. No problem here, it is a gmt one. The it parses the
interval, again no problem, intervals do not depend on DST.

But then you ask to add them, and timezone + interval is time zone
sensitive. And, counter intuitively to many, tstz does not store
timezone, it marks an instant in time, it just signals that
manipulation to the timezone must be TZ aware, so it does it in your
current timezone.

You probably have ( and if not, you should consider having ) your
partitions defined on GMT, so the time jump at the starty of the month
make it take one more ( are you in Britain/Portugal/Canary islands,
which are +0 in winter +1 in summer, IIRC ? )

If you want to scan exact partitions by specifying arithmetic limits,
set timezone to whichever tz your partitions are partitioned with, or
just name the partitions. If you just want to scan a date range (
thats's what I do a lot ), just trust pg to do the right thing.

Remember, specifying +00 in the input does not make the arithmetic
work in that tz, it works in your tz. I think you could achieve the
desired result by temporarily switching to timestamp without,
something like
('2020-10-01 00:00:00+00'::timestamptz at timezone 'GMT' + interval '1
month') at timezone 'GMT'
IIRC this converts to tstz to ts, then adds there, then converts back
to tstz, my results are:

cdrs=> show timezone;
 TimeZone
----------
 UTC
(1 row)

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

cdrs=> set timezone to 'Europe/Madrid';
SET

cdrs=> select ('2020-10-01 00:00:00+00'::timestamptz), ('2020-10-01
00:00:00+00'::timestamptz + interval '1 month');
      timestamptz       |        ?column?
------------------------+------------------------
 2020-10-01 02:00:00+02 | 2020-11-01 02:00:00+01
(1 row)

********* diferent UTC hours, same local. (02 but +02 and +01)

cdrs=> select ('2020-10-01 00:00:00+00'::timestamptz), ('2020-10-01
00:00:00+00'::timestamptz at time zone 'utc'+ interval '1 month') at
time zone 'utc';
      timestamptz       |        timezone
------------------------+------------------------
 2020-10-01 02:00:00+02 | 2020-11-01 01:00:00+01
(1 row)

********* Now same UTC hours, different local. ( 02 + 02, 01 + 01 )

If I had monthly partitions rolling at 00 utc, 1st one will nibble the
first hour of an extra one.

TSTZ is hard.

Francisco Olarte.