Niels:
On Fri, Apr 9, 2021 at 7:39 PM Niels Jespersen <NJN@xxxxxx> wrote:
> The answer to my original problem behind my original question which is how to query using utc timing in order to hit exactly the (utc created) partition, without changing the session timezone, would be like this:
You would have had much success had you stated your problem instead of
the seemingly academical question of queery equivalence, anyway:
> select f.* from f
> where f.r_time at time zone 'utc' >= '2020-10-01 00:00:00+00'::timestamptz at time zone 'utc'
> and f.r_time at time zone 'utc' < ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1 month');
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.
> 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 ).
Francisco Olarte.
