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 1rO5O1-001BiL-CC for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 00:27:01 +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 1rO5O0-00GR10-26 for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 00:27:00 +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 1rO5Nz-00GR0p-NR for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 00:26:59 +0000 Received: from mail-oo1-xc34.google.com ([2607:f8b0:4864:20::c34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rO5Nx-0016Ha-4y for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 00:26:58 +0000 Received: by mail-oo1-xc34.google.com with SMTP id 006d021491bc7-598a6b94925so833988eaf.1 for ; Thu, 11 Jan 2024 16:26:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705019215; x=1705624015; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:references:cc:to:from :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=AOlJq314h7xHg5u7vDHPfdnOLV9OFGbUH2eLV/l9d4Y=; b=MYFzExOWt+VvliBhNT7j0+ON3hrqLYDorf7Q8QB1PE4H4S6+A6p4xD2tW7Q9AjGyIg iLJldYbtFJuUjqkVHX9IC45LC6I137SYs6JZn/OVEd+C2vuMyzM+jsgB1w+WLyodSetv 7TA3C81UR45ICRUkCGU/1OKuTH7JdJ5RWiRZcPIhjCzwmMjmY8/drMWVHaIJPxiJGb0W xvVu88nDuDFrU7XH/rMrPWt9JjbJwv2QV6UKp2a7K9/DcChHWTzIFbI6YC5+83+Icf+n Dn5W2oqysVe1DhJaK7ALxbxzN2Vsgc3oBa68F7Wnf/lczaQADAu/or7lPVd7d4itItzs S/TA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705019215; x=1705624015; h=content-transfer-encoding:in-reply-to:references:cc:to:from :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=AOlJq314h7xHg5u7vDHPfdnOLV9OFGbUH2eLV/l9d4Y=; b=J7PaHQZKXhF5Gv0Z5OqTgEbWK+9QBo8lEDgfobYvDR9Pw9a7KFu8lvh2436UQ+9D6E pye7+2YB218+8Ch6C91YpQHwya+/uV/iESWb7rcDYmAh8rILjU47Mvdur9bsZIAC+dJB p102rRqNgZcbg64RAA8nNR7Q2oZt0S+U9DCANv/LHAs7TrFXDAlejM/rJnJS+P7KkqNN NXgCYUGFBjTa/RRhpVH628s/xW8+t75qAbrn+vg9AOz1NsNJCh5QuJwuddbaPp0o9HLA I3wJmkEChsset8DxWMIrPE7y0ZglN80MJw7G7DIVDaQrN85rtORo4alBvmZjw1ZuXs3r vHMg== X-Gm-Message-State: AOJu0Yx5UBSfytrFpVyEeQmboB/GqmnBXJfUVyUQ1FijsUChPtAopB8/ Yu495qtUhayqNU0gKOMvzA== X-Google-Smtp-Source: AGHT+IGjQRXsNhtUCbggQW69IG2NOyQWkund0c8bpX+ME6YUE3GtRE3Ee8nlIU6vSiaoUscxyXrB0Q== X-Received: by 2002:a05:6870:a11e:b0:206:7af7:101c with SMTP id m30-20020a056870a11e00b002067af7101cmr589566oae.81.1705019215016; Thu, 11 Jan 2024 16:26:55 -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 qw23-20020a0568706f1700b002043b415eaasm503088oab.29.2024.01.11.16.26.54 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 11 Jan 2024 16:26:54 -0800 (PST) Message-ID: Date: Thu, 11 Jan 2024 18:26:52 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() Content-Language: en-US From: Jim Nasby To: Tom Lane , Adrian Klaver Cc: Alban Hertroijs , "pgsql-general@lists.postgresql.org" References: <341384.1705017200@sss.pgh.pa.us> <8ad4100f-42d6-46d7-a7f1-17ac5864be7d@gmail.com> In-Reply-To: <8ad4100f-42d6-46d7-a7f1-17ac5864be7d@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 6:20 PM, Jim Nasby wrote: > On 1/11/24 5:53 PM, Tom Lane wrote: >> Adrian Klaver writes: >>> 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. >> >> Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields >> other than the prevailing value of the timezone parameter, so you >> really have to set that the way you want if you desire to use these >> format fields.  As noted upthread, SET LOCAL together with a (dummy) >> "SET timezone" clause in the function definition can be used to get >> the effect of a function-local setting of the parameter.  I don't >> know of another way to achieve that result above the C-code level. >> >>             regards, tom lane > > Sorry, I was implying that you could use the generated timestamp without > timezone as a string and supply the necessary timezone: > > select to_char(timestamptz(timezone('UTC',tstz) || ' CST6CDT'), > 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM') from tstz ; >               to_char > ------------------------------------ >  2024-01-11 23:29:00.0493300 -06:00 > (1 row) NEVERMIND... I see now that doesn't actually work. Perhaps we should add a variant of timezone() that handles this use-case... -- Jim Nasby, Data Architect, Austin TX