Re: Question related to partitioning with pg_partman

On Sun, Mar 10, 2024 at 11:31 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 1) The partition will be across one day(24 hours) it is just the times
> may confuse people. Per you example 2024-03-07 00:00:00+00 is the same
> time as 2024-03-06 19:00:00-05 for EST. The issue is that the +00 and
> -05 maybe ignored. Also it depends on the clients being consistent in
> using timestamptz.
>
> 2) You still have not answered what the datetime range(not date range)
> is that will be queried. If you have the partitions Midnight to Midnight
> UTC and the clients are querying Midnight to Midnight local time the
> query will not match the partitions.
>
>
My apology if not able to clearly put the details. Actually, the query
will always happen on a day basis i.e they can query from one day to 15
days transactions. But as you rightly pointed , the partitions can only
span from midnight to midnight in one timezone, and thus users who queries
the data from another time zone will mostly scan two partitions (even if
they just queries one days transaction data in their own timezone). And I
don't see an easy solution for this , which will help all users across all
time zones to scan only a single partition in the database, when they
queries data for a single transaction date.

And thus my question was, is it necessary to have the creation of
partitions to happen on UTC time zone only? and then whatever transaction
data inserted by the users from respective time zones will be stored in the
database as is and will be queried based on the user timezone (it may span
across multiple partitions though for a single user transaction date).