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 1rNyEP-00Ha3c-Tf for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 16:48:38 +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 1rNyEP-00DRVG-3s for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 16:48:37 +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 1rNyEO-00DRV7-PM for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 16:48:36 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rNyEM-0012nz-61 for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 16:48:36 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id C553F3200AD8; Thu, 11 Jan 2024 11:48:29 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Thu, 11 Jan 2024 11:48:30 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc: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=fm1; t=1704991709; x=1705078109; bh=kZRxIW5AvskfbmPQjRN8OSvdZeur7iVMHghptQNoryw=; b= Io+uQeSNNjMZqWcnE1g9esfEeLJ6hA8fymfzf3rHJlqDP1r69XpNq3bCz5A5UJpa tEtR3ma4DygriSOCOC8tk1Ws3zLWoGRx6UzzJcnH8jrWs/hNSbSRUBuMaznI76RC T05lieEdFV5SpvITlMwMW1YuKS+aZfECcw4I9rUr8KSv064DXMScp1tZSsmwAy78 ydCmmuSEwFJcVUl73QZVz4otMNwQ5brpERe+9ZEGc3RPsN3CfdFUQZPXFccVBy9o kHo4vjCva1+ScXhg1mHTpJwcrQUjyv/owaVwzQLW99R1hA2XvVLrclOnH0PnkguM KkkB4GvKSkeFYcquFVGbLQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc: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=fm2; t=1704991709; x= 1705078109; bh=kZRxIW5AvskfbmPQjRN8OSvdZeur7iVMHghptQNoryw=; b=e GVajCv3HAPQmmg9VNoySq1anp/qxqZlnW9S2QjPjae7iaIqfjB5g8WBcgPwaIgqT aQSpFePNP0NEaKFnSaafz+W/Zuikw3hnxVrZCWopkUWdunT/TCch00o8Vt8AhURm pRAhHiaUT065zq/4+pcNcWTNHdyMK7lpbR0n7tufyYp7/EBlTWyLkJc4+ogWXDFt EY6O8h69++4Ypa7HuHkpx37UgAdgHBoYd/ZmfXOCaQRhVuGf+Cku4Y4ovjwvMNAE UY1bZmKflZnMQhcR1bKpdhEWKakmEGs6Pwuv1szATSg1raxkAwBZS+UsZTwhfnvg 9REpFyIFjoKJ1vfWNoJhA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdeifedgleduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtjeertddtvdejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhephfeviefhveelffeftdehudekveefhfeftdegieef veetfffgfeehtdfftedutedtnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpe hmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 11 Jan 2024 11:48:28 -0500 (EST) Message-ID: <37804bda-ebe5-4786-be63-4d2a325368a6@aklaver.com> Date: Thu, 11 Jan 2024 08:48:27 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() Content-Language: en-US To: Alban Hertroijs , Karsten Hilbert Cc: "pgsql-general@lists.postgresql.org" References: From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/11/24 08:04, Alban Hertroijs wrote: > > In the above, I worked around the issue using a couple of > user-defined functions in PG. That should give a reasonable idea of > the desired functionality, but it's not an ideal solution to my problem: > > 1). The first function has as a drawback that it changes the time > zone for the entire transaction (not sufficiently isolated to my > tastes), while > > 2). The second function has the benefit that it doesn't leak the > time zone change, but has as drawback that the time zone is now > hardcoded into the function definition, while > > 3). Both functions need to be created in the caching database > before we can use them, while we have several environments where > they would apply (DEV, pre-PROD, PROD). > > Would a function that dispatches its calls to a suitable array of > hard-coded functions based on an IN parameter help any ? > > Karsten > > Well, probably, but we don't have many time zones that are relevant to > us. For that, the current functions would be sufficient. > > The drawback, as mentioned, being that we need to maintain those > functions in each deployment, which is a bit of a hassle (albeit a minor > one) because we need to customise both the TDV side and the PostgreSQL > side in that case. Our preferred solution would be to just add a few > entries to the TDV database-specific capabilities file (as described in > my initial message) Are you referring to?: "It currently have this: ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1) ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2) " I thought the issue there was maintaining the two Postgres functions? > Provided that such a solution is possible, that is. If not, my current > approach may have to suffice. > > The reason I decided to ask on the ML is that I'm finding it hard to > believe that this transformation would be this difficult, so I expect > that I must be missing something. > > Regards, > Alban Hertroys -- Adrian Klaver adrian.klaver@aklaver.com