SV: where clauses including timstamptz and intervals

>-----Oprindelig meddelelse-----
>Fra: Francisco Olarte <folarte(at)peoplecall(dot)com>
>Sendt: 10. april 2021 09:31
>Til: Niels Jespersen <NJN(at)dst(dot)dk>
>
>Although it is equivalent I would suggest to use:
>
>f.r_time< ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1 month') at time zone 'utc' ;
>
>optimizer should treat both the same, but I'm no sure it can recognize it, and it may not see your condition is of the type "field < constant" to select index scans and similar things. Also, you can refactor your calculations into an stable function for greater clarity.

The plannes seems to be able to figure this out nicely.
>
>> I think maybe it's better to just change the session timezone when this need arises.
>
>Maybe. Also, I do not see the need to query exact partitions by tstz range, but assuming you do it may be much easier to query the catalog for the partition and then query the partition. ( In many years of working with time-partitioned tables I have never got the need to build a query to hit exactly X partitions from times ).
>
I need to hit an exact partition, because the query makes some QA on data coming from a file that fits that exact timeslot. File and partition must match exactly because the file may be raplaced later and the partition truncated.

Thank you for your insights.

Niels

>Francisco Olarte.