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 1rOL6j-003MWn-W2 for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 17:14: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 1rOL6j-003uNX-1s for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 17:14:13 +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 1rOL6i-003uNP-0e for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 17:14:12 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rOL6f-001D6l-92 for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 17:14:10 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 529B45C00CE; Fri, 12 Jan 2024 12:14:08 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Fri, 12 Jan 2024 12:14:08 -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=1705079648; x=1705166048; bh=B16MNXxkcGw2IwXPLgbJqp2NoZ3JNh7TU6AFSn3yspw=; b= KajypO+jCLSOipx0KGmSmDjozLmSgGTrWs9qfPqftfyL29r+3mDaS98NGqHXHVlP dpmC3pp9V4cdqaH8WVd89L55YnvmLRKZxDxiAEGR1lnT2WGdpx0rCFKtQjlpH1hW g77MD/p7Bgf5ThQgpV22ygic1CfUUknA3HwPHZX5Ug8FMoFS4R0S8+elYQF700+k MMV/z53/nIlSYoZHCVe4D8YmVga/YLaar5nyOJIKPx5ivc7OA1yWbL2NLUxS5Egi q4uUqog5ME+36ZbUmFP+ShRTspiDflHbS6dtcDcaGjNs5ZbT3cBj+HBqRP9TTBYI w7r6h8bbS8UMlM08NypgEA== 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=1705079648; x= 1705166048; bh=B16MNXxkcGw2IwXPLgbJqp2NoZ3JNh7TU6AFSn3yspw=; b=0 c4gxOwivyTGBfMtbs0w5cOtwv9P8cXOhNdX3KBSqka2mTTDTf7qnte9R9epz/y3x UydCu4H/tr7gHynzvrnQecZR40LJjqUjLNu9YDzre9jFsiMGyJyANVdP+NvZhP55 vLxdxOPxYbRh6UzqQB+9vXg6Ht5jF+y35otU3mjHhfNo1knyQ3h47qZJcHtOQbMx FZBYQvYKK8dRAgUszp2+6Qyw3TrOX0rhY6UBzAiezX09KW/8N66oxoUIuKCjGVVG DkLP+m9tLsCXab2Q1pPlPTBsJLyDafAJ9CtxmVViLguUyNsm54Wr2FRZa9wNjSJR 9v7MjGrstCNYRNsi4l5Lw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdeihedgleejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepfeegfeeiuedtgffgteeggfehkeejheetieeliefg teeikeejvdeiveeigfehvedtnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpe hmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 12 Jan 2024 12:14:07 -0500 (EST) Message-ID: <2804e2d7-3c27-4097-97d9-d05377dbb31b@aklaver.com> Date: Fri, 12 Jan 2024 09:14:06 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() To: Alban Hertroijs Cc: "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: 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/12/24 00:51, Alban Hertroijs wrote: > Following up on my own mail from yesterday evening, here's the output > that shows the function using set_config 'leaking' the timezone change > to outside the function (the first select vs. the 2^nd  select) into the > (same) transaction, whereas the function with the time zone bound to the > header does not (the 3^rd  select). > These are all from a single session, ran consecutively - a straight > copy-paste from psql. Alright I see what you are talking about. > > ciscache=> select ToDatetimeOffset(current_timestamp, > 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-MM-DD > HH24:MI:SS.US0 TZH:TZM'); >           todatetimeoffset          |              to_char > ------------------------------------+------------------------------------ >  2024-01-12 09:41:44.7019350 +01:00 | 2024-01-12 09:41:44.7019350 +01:00 > (1 row) > > ciscache=> select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 > TZH:TZM'); >               to_char > ------------------------------------ >  2024-01-12 08:41:54.4739800 +00:00 > (1 row) > > ciscache=> select ToDatetimeOffsetNL(current_timestamp), > to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM'); >          todatetimeoffsetnl         |              to_char > ------------------------------------+------------------------------------ >  2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00 > (1 row) > > Regards, > Alban Hertroijs. > -- Adrian Klaver adrian.klaver@aklaver.com