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 1qo2bY-00CtO1-RD for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 14:12:00 +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 1qo2bW-00G1sh-1a for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 14:11:58 +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 1qo2bV-00G1sY-MR for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 14:11:57 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo2bS-0000AZ-W5 for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 14:11:57 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 394EBoNU2582289; Wed, 4 Oct 2023 10:11:50 -0400 From: Tom Lane To: Steve Crawford cc: Lincoln Swaine-Moore , pgsql-general@lists.postgresql.org Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones In-reply-to: References: Comments: In-reply-to Steve Crawford message dated "Tue, 03 Oct 2023 20:36:12 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <2582287.1696428710.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 04 Oct 2023 10:11:50 -0400 Message-ID: <2582288.1696428710@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Steve Crawford writes: > On Tue, Oct 3, 2023 at 7:33=E2=80=AFPM Lincoln Swaine-Moore > wrote: >> 5) Ideally, the solution would not involve messing with the >> server/connection's value of timezone. (Though I would be interested if >> there was a solution that relaxed this constraint and was relatively >> safe/compatible with transactions and psycopg2.) > Note that setting the time zone is a client/connection setting so if you > set it within a transaction, it will stay set when the transaction > concludes. But time manipulation is tricky and trying to DIY reinvent th= e > wheel is painful and often buggy. Let PostgreSQL do the work for you. Expanding on that philosophy: you should be able to set the timezone locally within a function, so that it wouldn't be that hard to make a wrapper for generate_series that emulates the 4-argument version added in v16. Rather than messing with manually saving and restoring the prevailing zone, I'd let the function SET infrastructure do it for me. Sadly, that SET clause only takes literal constant arguments, so it'd go roughly like this: create function generate_series(timestamptz, timestamptz, interval, text) returns setof timestamptz strict immutable language plpgsql as $$ begin perform set_config('timezone', $4, true); return query select generate_series($1, $2, $3); end $$ set timezone =3D 'UTC'; Setting the zone to UTC is a useless step, but that triggers restoring the previous zone when the function exits; simpler and probably faster than coding the save/restore explicitly. Side note: whether this is really "immutable" is a matter for debate, since time zone definitions tend to change over time. But we chose to mark the new 4-argument version that way, so you might as well do so too. regards, tom lane