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 1rO2Ib-000fCt-C5 for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 21:09:14 +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 1rO2IZ-00EuWk-BV for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 21:09:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rO2IX-00EuWc-Cm for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 21:09:11 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rO2IU-0014Un-0g for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 21:09:08 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.west.internal (Postfix) with ESMTP id 581643200B77; Thu, 11 Jan 2024 16:09:03 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Thu, 11 Jan 2024 16:09:03 -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=1705007342; x=1705093742; bh=idiUW7jO2jKr8rzHuGq0SJoaw31AmBQ75uD47nVS+ds=; b= mdRKPOtaV/bp5KEtv+C5TAnm+zupU9ZqDM759AhmvSuMT1PR4FINrnl7YMWqJFlX Su+fEl87fLEWcGe9K5h/j2hKZ/NoBHRSTcr6rXUfZMtuC0OOkKlZfLBBN25PEtre KhNESQn6usE8+u3KupxNlyQZ9uCmdZJz9/OG8wNPEcOXE6yPxTT0kjzMcVelyp9a FMJ1BMt7o0ZzWIdhWaJZWl2jIQUOMbmgJ3cVuSnEhlnsgC6H739CfArIDNlfleqZ ypzUC5h7ygPR2U/W/w0yNQ3Cu1ryBy0W6OdIWGHHGQussLFVo/ut0ZbGb6K4ccQg QuMh1I1gkUE9G8M/9xsRwQ== 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=1705007342; x= 1705093742; bh=idiUW7jO2jKr8rzHuGq0SJoaw31AmBQ75uD47nVS+ds=; b=z k68AxXZu9iKlVFtbC/qMHuLyg5bqLtiNfjGXA6IiK6rJN2dsLbvJIpR9SXb3tHvf urPRv8o2p+YExIQpP7m4G1pxDWi3STjwPDZsXLp2NxAiXO8Q/DDilFsHba5EXQEB rvn8dsSmi7Dx2IFm7ehcUVvjYc3p9WNuhYwY1AS5cBN0xNZi61uGgqiqFBbuN2zX xUeckVG8JJINHDoGXtEVEJ43bsxk8Ho4260JIF3+pJRpySY8m6bDllH3rBAjGvrW kaC39MP4DiUsDShHzL4aBb4BCuu4wl9nna19GJZ1BDvJ81YLxSl2t7Ipq0FtJ1sm 191MGlhDo5REyOZ2uU4tA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdeifedgudeggecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvvehfhfgjtgfgse htkeertddtvdejnecuhfhrohhmpeetughrihgrnhcumfhlrghvvghruceorggurhhirghn rdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomheqnecuggftrfgrthhtvghrnhepfeegfe eiuedtgffgteeggfehkeejheetieeliefgteeikeejvdeiveeigfehvedtnecuvehluhhs thgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklh grvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 11 Jan 2024 16:09:01 -0500 (EST) Message-ID: <5bd216b2-4c55-4832-8b9c-bbda9b636bd4@aklaver.com> Date: Thu, 11 Jan 2024 13:09:00 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() To: Alban Hertroys Cc: Alban Hertroys , "pgsql-general@lists.postgresql.org" References: <11b56239-9f18-4b94-aeac-395648a2bfe3@aklaver.com> <752055E6-31DA-404F-AB5E-0BAB83836108@gmail.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: <752055E6-31DA-404F-AB5E-0BAB83836108@gmail.com> 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 1/11/24 11:04, Alban Hertroys wrote: > > > I didn’t succeed in calling SET LOCAL TIMEZONE from within the function. Could be I missed something, then Google (stackoverflow) pointed me to set_config(). CREATE OR REPLACE FUNCTION public.tz_fnc() RETURNS void LANGUAGE plpgsql AS $function$ BEGIN SET LOCAL TIMEZONE = 'UTC'; RAISE NOTICE '%', to_char(now(), 'OF'); END; $function$ test=# begin ; BEGIN test=*# select public.tz_fnc(); NOTICE: +00 tz_fnc -------- (1 row) test=*# show timezone; TimeZone ---------- UTC (1 row) test=*# commit; COMMIT test=# show timezone; TimeZone --------------------- America/Los_Angeles (1 row) > > I did manage to apply it to the second function header, which I think behaves such that the time zone change stays within function scope. Right now I’m not 100% sure that I verified that. More to check tomorrow. > > Frankly, I do hope that you’re right here, that would make my work easier. > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find there is no forest. > -- Adrian Klaver adrian.klaver@aklaver.com