On 3/8/24 00:23, sud wrote:
Starting a new thread...
Something interesting and not sure if its expected behaviour as below.
We are also confused a bit here.
In the below example we created two partitioned tables on timestamptz
type columns with different time zones and the child partitions are
created appropriately with boundaries as one mid night to next mid night
of a day and so on. But when we change the time zone and query the data
dictionary views again, it shows the start and end of the partition
boundary as not midnights but different times of the day's values.
So I was wondering if this can cause us any unforeseen issues in the
long run while creating the partitions though partman and persisting the
data into the tables from the end users then querying those and having
queries properly partitioned pruned?
or
should we always set the local timezone as UTC always before running or
calling the pg_partman/pg_cron process which creates the partitions?
Mainly in a database which serves global users sitting across multiple
timezones. And same thing while inserting data into the table, we should
use UTC timezone conversion function. Can you please confirm.
'2024-03-07 00:00:00+00' and '2024-03-06 19:00:00-05' are the same time
as is '2024-03-07 00:00:00-05' and '2024-03-07 05:00:00+00'.
Still I would think for sanity sake you would want to stick with UTC.
And while checking the timezone using the "show timezone" function it
shows the local timezone, so is there any way to see postgres DB the
server timezone?
show timezone is the currently set server timezone.
select reset_val from pg_settings where name = 'TimeZone';
would show you what the value would be reset to, e.g it's 'default
value. For more information do:
select * from pg_settings where name = 'TimeZone';
to see where the 'default' is set.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
