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 1rO5IM-001Asg-ND for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 00:21:11 +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 1rO5IL-00GNk3-Q3 for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 00:21:09 +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 1rO5IL-00GNjv-Fp for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 00:21:09 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rO5IE-0015t5-IN for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 00:21:08 +0000 Received: by mail-ot1-x329.google.com with SMTP id 46e09a7af769-6dddf12f280so2589182a34.0 for ; Thu, 11 Jan 2024 16:21:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705018862; x=1705623662; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=aPYU98t1DvvSPbGrnqHMvi96vTuomMdKHO6HIYetJrE=; b=b0EEb1tZAYJdLqHU6Q00zZWTpeB1UC0ZxVNkMctmGxGaB8/ZjOJDyr5TR1H7i+Wn7U M2od6R9QeV6YlKWbnkCnXv+h99d7SRVuQnJiXwNHsIpoT6mlZPa4IeuyWAp8exHXFy2U eAS2I1aOUSI1nCJ60wndM2+Um225dM1TxTXC1iw6FVQHUeybKl0+f5o6TIZKmveu0Fy7 Ijxudj3U4pCKYJJmC9cftVTmfNVE4Rbu32odJ/9EhKIRjUDQn0duMYjdLAPLWBROdA3G ymCHeS2Om0WYjKrSOl5XW/2QgIGlnE8GPpJ/W3RqsexswTjxcArb8oC++pvUlnoOvcMf y/Bw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705018862; x=1705623662; h=content-transfer-encoding:in-reply-to:from:references:cc: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=aPYU98t1DvvSPbGrnqHMvi96vTuomMdKHO6HIYetJrE=; b=c9uIHH/kcwy30Lk233oS2o9aBJ3Kkn+YPUhoJUyMb5VBVcdqMbGhf96oyA0noJkx74 UHpuyPaJGbmXakRngv+s0jXHZeLctMgxAuIAJ/IPj3g4SinU64me8dfceY3sAaetlNOT ikY1ubH/cT/SjOsfr98IQLPY6NDdFf2hQ1GYJvBMZY3VK0KMJTNDBw9q4xLPaPXkMYcy jYRUReDxPiYoAQzfY+NStHPpgbaVlvfkAaLhHXK+zwM2dQwd7Uv7ho2SK0ljpNt/Esrq McWiN08fk0VNNF50x3p+tjTLET2l0B9/SrkUQPQzW2ZFl5KhZLaTOztzsZ5xtR2gJ3Td Oqsg== X-Gm-Message-State: AOJu0YyyJeyEmp1aYy2bcrXu+PJJNN9ANSaR1+vGYETgcl7y9r0mwo77 Ges3DTlg19i0mWeGFNsG5dlYmWQe2j2O X-Google-Smtp-Source: AGHT+IFMIh5n/0QU9hkgXk8c/q2QiqUAGYiux9nbu9OZhDqT2CNpTL8BBE08tkOkln2hpdbisr0ZRA== X-Received: by 2002:a05:6830:1402:b0:6dc:386:a236 with SMTP id v2-20020a056830140200b006dc0386a236mr642579otp.47.1705018861775; Thu, 11 Jan 2024 16:21:01 -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 v18-20020a05683018d200b006ce46212341sm371954ote.54.2024.01.11.16.21.01 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 11 Jan 2024 16:21:01 -0800 (PST) Message-ID: <8ad4100f-42d6-46d7-a7f1-17ac5864be7d@gmail.com> Date: Thu, 11 Jan 2024 18:20:59 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Time zone offset in to_char() Content-Language: en-US To: Tom Lane , Adrian Klaver Cc: Alban Hertroijs , "pgsql-general@lists.postgresql.org" References: <341384.1705017200@sss.pgh.pa.us> From: Jim Nasby In-Reply-To: <341384.1705017200@sss.pgh.pa.us> 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 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) -- Jim Nasby, Data Architect, Austin TX