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 1rO4lR-0015Pa-VQ for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 23:47:10 +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 1rO4lP-00G7pi-KO for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 23:47:07 +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 1rO4lO-00G7pa-Pi for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 23:47:07 +0000 Received: from wout3-smtp.messagingengine.com ([64.147.123.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rO4lK-00161G-EW for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 23:47:05 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id 014B8320090C; Thu, 11 Jan 2024 18:46:58 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Thu, 11 Jan 2024 18:46:59 -0500 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=fm1; t=1705016818; x=1705103218; bh=PRMaHdMD0Y9/JfOMMmN2KNfcsVgvq4BwkPkl9zJ1e/I=; b= cuInTwqbRzahpSdrcwzUr3sJyCkwFnU5Uj/I3o+bTj7F2dIxnTOF1+Z2RFoTjrNk ItAee+qQPZXJJhKC8H2vgUfNV2LVya1j3lXMTbnI31H3qL7Jj5r1RJOeahBFII/K l7VDk1Qzf/UOZK82uOf5ZQIu17k+vFGlGeQ33rtACbaS6A5pCYR+sbbvPK4wGY7t 1HpoUFRmuASzrjS3nAHvBpPtmGLX8AaVwnRZ+y7o7Ve3DAr2BdH1ercFT9Fz9vfA cc2nx9EhgV/FashpOmoYUrmU8PNIZSBpRESioInoo5GZcO1z5LSydGbbS9V5/XpE 4ZYeVvOGtCb73R7XnlbHPg== 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=fm2; t=1705016818; x= 1705103218; bh=PRMaHdMD0Y9/JfOMMmN2KNfcsVgvq4BwkPkl9zJ1e/I=; b=u iDbxwfnRJtwkkLrbqnMd8l2Vquhrv020g35PPv7yQQ/EbFc+mta6iQHtppD5CpoG MWBbgLAOonfMYltodZFjMAtLICQynmB5WCgEwXAQhGex9WiwhhrG7/f6OdrnM89v ebkpsn4juDydQyuotITdiz8fw//cM9KvDOXUODmKZ/ScBrt8to54OpLrJy5ZMmVL WdkgYWcghwfVXF+qV3Mrxp+dAj9OVVnMIptT94h22FLc5pgJw5/NK2PGEduU32qL I5AmAhvrlr11I4TQuyBKJIsuKQDhcnxuKRaCJ4J9B1JAAU7cfopnCuqnLL9EjACP daKDqcfvyLUyGb9H2uJ2A== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdeigedgudehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeelgeevkeekkeeuiefgtdevieeluefhfedufeetkeej ffekjeeujeehgeehgeektdenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 11 Jan 2024 18:46:57 -0500 (EST) Message-ID: Date: Thu, 11 Jan 2024 15:46:56 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() To: Jim Nasby , Alban Hertroijs , "pgsql-general@lists.postgresql.org" References: 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/11/24 15:32, Jim Nasby wrote: > On 1/11/24 9:06 AM, Alban Hertroijs wrote: >> I'm basically looking for a one-liner to convert a timestamptz (or a >> timestamp w/o time zone if that turns out to be more convenient) to a >> string format equal to what MS uses for their datetimeoffset type. I >> got almost there with to_char(ts, 'YYYY-MM-DD HH24:MI:SS.US0 >> TZH:TZM'). Unfortunately(?), the server lives at time zone UTC, while >> we need to convert to both UTC and Europe/Amsterdam zones. The above >> always gives me +00 for the TZH output, while it should be +01 now and >> +02 in the summer... > > The issue here is that timestamptz doesn't store the original timezone; > it always converts whatever is passed in to UTC and stores that. When > you read the timezone back, by default it will be in the timezone > specified in the TimeZone GUC. While there's a bunch of ways you can set > that, for what you're looking to do I don't think any of them are > appropriate; instead you want to use either AT TIME ZONE or timezone(): > > create table tstz(tstz timestamptz); > insert into tstz values(now()); > SHOW timezone; >  TimeZone > ---------- >  CST6CDT > (1 row) > > select * from tstz ; >              tstz > ------------------------------ >  2024-01-11 17:29:00.04933-06 > (1 row) > > select timezone('UTC',tstz) from tstz ; >          timezone > --------------------------- >  2024-01-11 23:29:00.04933 > (1 row) > > select tstz AT TIME ZONE 'UTC' from tstz ; >          timezone > --------------------------- >  2024-01-11 23:29:00.04933 > (1 row) The problem with this is as mentioned here: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT "timestamp with time zone AT TIME ZONE zone → timestamp without time zone Converts given time stamp with time zone to time stamp without time zone, as the time would appear in that zone." So when you do something like: test=# set timezone = 'UTC'; SET test=# select now() AT TIME ZONE 'UTC' ; timezone ---------------------------- 2024-01-11 23:44:46.021986 (1 row) test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM') ; to_char ------------------------------------ 2024-01-12 00:44:57.5421420 +00:00 (1 row) You end up with string that does not the correct offset as the AT TIME ZONE outputs a timestamp not timestamptz value. -- Adrian Klaver adrian.klaver@aklaver.com