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 1rO4Wy-001323-Pi for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 23:32:13 +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 1rO4Wx-00FrIH-CK for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 23:32:11 +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 1rO4Wx-00FrI9-1j for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 23:32:11 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rO4Wu-0015tE-Dg for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 23:32:10 +0000 Received: by mail-oi1-x230.google.com with SMTP id 5614622812f47-3bbd6e37a9bso4887498b6e.0 for ; Thu, 11 Jan 2024 15:32:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705015926; x=1705620726; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=0mFl5iCbsDYOlWfWgUxhpzhXAwGFnJhEKdSUjMXo830=; b=birxzcMXucTapH4dTykA447D10SZBXq+75SOXcQnt6Sw+st2K0o4+3sXNe/EDq5b40 Fx/A7sI7X+QC6+fmBZP6FitpWeq6hkfbjE2jZ0OjcAeZb5FbnmVRo4wHjPptYqpcEG97 5eXhZDQOdSquS+puTvh34svHrWSEjIsFlAzoI34CXgQgMQ6VMNIyCcZHFQHgyAAXBCtA UsLy9fCof/v3zG5M7XCA0Ko7weVO2JUaIXa8xeJ58qYo2/D23CvN//yKcmNsnowQoe/S tQUXwMDIznlKvRh0VcP1z5YPl0SfVSlHju7DwVBx3xrd/Jn7gmg0KNluQEqIMWcCxiI7 X2HA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705015926; x=1705620726; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=0mFl5iCbsDYOlWfWgUxhpzhXAwGFnJhEKdSUjMXo830=; b=CcWeBmA9Wchwey+FUO5shroQbGjAQfmnYTwD/gTLh2hOrqp1VrRdfytWU3wt8piHNd wl9Sq3GwbhmAazS12rgnVEu4C3JVUUb02jJhgPSwGXWOqeTwJcOFNWZZCw9fW9wZXcIe BxVb1FNqLzNe/zSw+W+5tl12LhRGAEcCAaW2197QWOSUoAJiiMUH5JYa02COQXmLpyR7 3oszqJt7FZEFUpxrga9nG/5mvpxiXIzdNQW5s3DMc5l5KNcQuufX9Ru8ROF5XgqGXqYa qu3x0ay5wfLbC3ehT6I0DEKHGxP7vltn28pzfpTtbLz8mpatjkbQY7QEMvKzqxLv4XXF P+FQ== X-Gm-Message-State: AOJu0Yx4VvOz7o1Sj/F1Sa50w/8FWDLlQFBWvbiPvdZGsnndyFh5l9lG 9+riBnbbDwB3Zm5oB/agSg6BGaxNTw== X-Google-Smtp-Source: AGHT+IGcEu+Za9F3Wf/pnnatWVO/0cnB2DtiT9P8Ga2AhHvLVRtEhF3Z7JGOAS58GsYojzW4G/tw3g== X-Received: by 2002:a05:6808:2107:b0:3bd:2101:90e7 with SMTP id r7-20020a056808210700b003bd210190e7mr122630oiw.25.1705015926247; Thu, 11 Jan 2024 15:32:06 -0800 (PST) Received: from [192.168.2.15] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id be36-20020a05680821a400b003bd3c878a99sm344939oib.13.2024.01.11.15.32.05 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 11 Jan 2024 15:32:05 -0800 (PST) Message-ID: Date: Thu, 11 Jan 2024 17:32:03 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() Content-Language: en-US To: Alban Hertroijs , "pgsql-general@lists.postgresql.org" References: From: Jim Nasby 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 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) -- Jim Nasby, Data Architect, Austin TX