Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjMYu-0055zD-Mv for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 17:02:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rjMYs-0057bU-6N for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 17:02:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjMYq-0057bM-I7 for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 17:02:10 +0000 Received: from fhigh2-smtp.messagingengine.com ([103.168.172.153]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjMYl-003mwp-8h for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 17:02:07 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailfhigh.nyi.internal (Postfix) with ESMTP id 4256011400AB; Sun, 10 Mar 2024 13:02:00 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Sun, 10 Mar 2024 13:02:00 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1710090120; x=1710176520; bh=fI4e7Q4Ug31G1Uw2gw25a69YdWCdap+XMM8aYaZR6CQ=; b= OFK8h/La3hVZhaJWym/C4jxnbfne9LG+J2hgagno4wbN/5h8ViXkufQxbh6iZK6g a3KBVdiuIAC9Snnw5GNzkcAxkaqS+nYoGB8M76JVcprqDRWRAMP/vVH4oJfPYJGM iOSzcrgYeAeUtzF2ML6mIpZ62+aIkPdZ5oEUu+Axq9OCEykEKwlP6/6HEOYEnup0 xam9LuwzgpH2ruQpR65y8gcLkDwm0GCUsXr1ccBfo3bCEB+g4dWDbQ2BiWIXwy0W /j/r7yFiyT1LSVYRY4zba2OdeTFBbGtkPdJYvvUBtoofpNH3M9xLmQMLpnP9VH9C IMDJQaTtIY016Zku7+Fh+w== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1710090120; x= 1710176520; bh=fI4e7Q4Ug31G1Uw2gw25a69YdWCdap+XMM8aYaZR6CQ=; b=T grWP7UU17BfUnWJ1+xQDkpmXI7L/lt5lqIjftI6G+iwTTvRLZd+jss+9LvQzaL/t ftwmDbtFmGqn2rSL61jmihPvU1uvNactlS0bN3QHAwTNcpdxAe7fJ+8SU9VCcpqP A0Ed19de5kC+GIQ0a5LGVhfbPhHQwKLNpikWh28adWJc88WfzLkeNvGFmAkzKrUg Pid3tvn684o3VnWLH3NEiMMUC/P42csVTicaiSBHUynMC9o248YxUbe9ucP1KwmK vRuPTz+9GnXe2R9L0qv2SWPDevEEtOB0th6B7d4XVvPE4Sy8H0o+lGqFuXYp5q2+ TjrvbF9JyVrDxphXlr/Tg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrieelgdeliecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesthekre dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeffleegieefgf evudehtdfhkeeutdffjeevgeffgeejvedthefgudeiteefheejheenucevlhhushhtvghr ufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 10 Mar 2024 13:01:59 -0400 (EDT) Message-ID: <46b8c853-a98c-41ee-b3f3-e1fcf11f26db@aklaver.com> Date: Sun, 10 Mar 2024 10:01:58 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Question related to partitioning with pg_partman To: sud , pgsql-general References: <7ca9af73-6cb2-4ac0-8dfb-b83a50c64363@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/10/24 05:12, sud wrote: > > > '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. > > > > Thank you so much Adrian. > > In my example in the first post, I see, if someone connected to a RDS > Postgres database and run the create partition command using pg_partman > by setting the timezone as "UTC", the 7th march partition looks to be > spanned from "7th march midnight" to "8th march midnight", when queried > the partition_experession from the data dictionary view. Which is correct. > > And same information if someone querying by setting the timezone as EST > is showing spanning from "6th march 7PM" to "7th March 7PM". And this > can cause sometimes the partition may shift to other days all together. > Similar differences happen if creating the partitions using EST timezone > initially and then querying the data dictionary from UTC timezone. The above is at odds with your example below which has the correct values: 2024-03-07 00:00:00+00 = 2024-03-06 19:00:00-05 > > So my question was, if in these types of scenarios, we should follow a > standard approach of setting the timezone as UTC in such a type of > global user use case, while the system can persist data from multiple > users sitting across different time zones? So that the boundary(start > and end time) of each of the range partitions will be set as consistent > in one timezone across all the partitioned tables? You need to first determine what your time frames are going to be? 1) Midnight to Midnight in UTC will be consistent when viewed in UTC. It will not be when viewed in other time zone +/- the offset from UTC. 2) Or Midnight to Midnight in the users time zone, in which case the UTC values will differ. You have to decide which of the above is your goal. The bottom line is by definition the local wall clock time will not equal UTC, GMT excepted. This comes down to what the purpose of the partitions are? In other words how do you want to organize the data? > > And even while inserting the data , should we set the timezone to first > UTC and do the data load ? > > ******* Partition created by pg_partman by setting timezone as UTC > *************** > > *UTC* > *Partition_name                         Partition_expression* > test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00+00') TO > ('2024-03-08 00:00:00+00') > > when queried the partition_expression using EST .. > > *EST* > *Partition_name                         Partition_expression* > test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-06 19:00:00-05') TO > ('2024-03-07 19:00:00-05') > > > ******* Partition created by pg_partman by setting timezone as EST > *************** > > *EST* > *Partition_name                         Partition_expression* > test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00-05') > TO ('2024-03-08 00:00:00-05') > > when queried the partition_expression using UTC .. > > *UTC* > *Partition_name                         Partition_expression* > test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 05:00:00+00') > TO ('2024-03-08 05:00:00+00') > > ******* > > Also i see both the "setting" and "reset_val" is showing as local > timezone only. If we set the timezone to a different value than the > local timezone then it gets updated on the "setting". > > Regards > Sud -- Adrian Klaver adrian.klaver@aklaver.com