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 1rO0MH-000Lwv-0a for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 19:04:53 +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 1rO0MG-00E8jS-1S for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 19:04:52 +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 1rO0MF-00E8jK-N1 for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 19:04:51 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rO0M8-0013Wh-DF for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 19:04:50 +0000 Received: by mail-lf1-x12b.google.com with SMTP id 2adb3069b0e04-50edf4f478eso1488400e87.3 for ; Thu, 11 Jan 2024 11:04:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1704999882; x=1705604682; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=iOM7qWnbgv5EL1xAgS4DvrbA/2VAfFoKUElnhl0g+2A=; b=imRooiEhjd4oLY/uRy8nO9Pr645HSraUoQFaoeT7o/GwA5a7L/wtvz43KKzqBwFrBS FcxURAR2X2jVSAMgAUsWAydOCXLd6ldO0vCa+ZQq0VzJ+m4T3aq7MUpyYAtF0a8rHjfV /IjmM9nG9IRcf9Xyi+kTqARlzZdGcTTsf0iptvuQJJoBsB4wlEP2BD0y8pTcNdsaCER3 +A3x/tzLiQ9nD+hsvFpHoOpPzQYY0GjCUMjyRUHf4OwdOQYiNSzfmRPswJelnc+rWKGc as4ZDyrgXeiDftUH3Abpc3e15L7AD6Zdz7/jr7lm+gyXwR5ClnqBU+6kDDTESQCAhQQw YccA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1704999882; x=1705604682; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=iOM7qWnbgv5EL1xAgS4DvrbA/2VAfFoKUElnhl0g+2A=; b=I95FYSK9stuBvvGB1cFzsgTRzE/DGFNB8lDWe0AymitKRh+3/eAOIyYCyvZTr+AL5A Ucn/rqOOnS6/z61OFWGr3cvyZlsRLWSwLL187/K1cDIOlfeq5WbnocI53HkgUNZdstP8 vgEp5DqiYfr8BJ6XE5YUBoDE1Yzg5o5ZWP2fPOVHhhJIfFlM/1I+KgmZ45rfPtZ7vvWV eb4XFNl1xNJuCmmF+XbeKjQg19EvoJ7zDZrBDxSGCI5h8QiAsb1reSoLbRP8MlX8IsPU VcbBN2/46Cx19Zv/nVE41kbCsLB5zM4YtwACg63h5ESoIiic9nt5IqLhb0SF+qUzUVhf nLXw== X-Gm-Message-State: AOJu0YwWEQfFGaMzkxDZMsC1Gae2iihhg4rCrcDZU8ftTfu4/QLgK9eu 8q32LRGAhK2xcbhsNJFkdjM5CHvrAqo= X-Google-Smtp-Source: AGHT+IF85k9Ffj+0L7vq50C9baVg5KGZUpBIwVsgFVN4LkPnG99qYql1MCBYiO23cVeU4Gqjexajqg== X-Received: by 2002:a05:6512:3d25:b0:50e:6a70:24fa with SMTP id d37-20020a0565123d2500b0050e6a7024famr92982lfv.6.1704999882212; Thu, 11 Jan 2024 11:04:42 -0800 (PST) Received: from smtpclient.apple (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id lt18-20020a170906fa9200b00a2ac199ff67sm897535ejb.170.2024.01.11.11.04.41 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 11 Jan 2024 11:04:41 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: Time zone offset in to_char() From: Alban Hertroys In-Reply-To: <11b56239-9f18-4b94-aeac-395648a2bfe3@aklaver.com> Date: Thu, 11 Jan 2024 20:04:31 +0100 Cc: Alban Hertroys , "pgsql-general@lists.postgresql.org" Content-Transfer-Encoding: quoted-printable Message-Id: <752055E6-31DA-404F-AB5E-0BAB83836108@gmail.com> References: <11b56239-9f18-4b94-aeac-395648a2bfe3@aklaver.com> To: Adrian Klaver X-Mailer: Apple Mail (2.3774.300.61.1.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 11 Jan 2024, at 17:43, Adrian Klaver = wrote: >=20 > On 1/11/24 07:06, Alban Hertroijs wrote: >> Hi all, >=20 >> In the above, I worked around the issue using a couple of = user-defined functions in PG. That should give a reasonable idea of the = desired functionality, but it's not an ideal solution to my problem: >> 1). The first function has as a drawback that it changes the time = zone for the entire transaction (not sufficiently isolated to my = tastes), while >> 2). The second function has the benefit that it doesn't leak the time = zone change, but has as drawback that the time zone is now hardcoded = into the function definition, while >=20 > I don't think the set_config and SET are acting the way you think they = are: >=20 > = set_config(https://www.postgresql.org/docs/current/functions-admin.html#FU= NCTIONS-ADMIN-SET) >=20 > " > set_config ( setting_name text, new_value text, is_local boolean ) =E2=86= =92 text >=20 > Sets the parameter setting_name to new_value, and returns that value. = If is_local is true, the new value will only apply during the current = transaction. If you want the new value to apply for the rest of the = current session, use false instead. This function corresponds to the SQL = command SET. >=20 > set_config('log_statement_stats', 'off', false) =E2=86=92 off" > " I tried this like so: select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), = to_char(current_timestamp, =E2=80=98YYYY-MM-DD HH24:MI:SS.SU0 = TZH:TZM=E2=80=99). The result of the second call was based on time zone = =E2=80=98Europe/Amsterdam=E2=80=99, where it wasn=E2=80=99t when called = outside the transaction (when it was based on UTC corresponding to the = server time zone). So the time zone set with set_config(=E2=80=A6, =E2=80=A6, true) = appeared to leak out of function scope and applied to transaction scope = (as described in the quoted text). For brevity I could run that query tomorrow when I=E2=80=99m back at = work. > SET(https://www.postgresql.org/docs/current/sql-set.html) >=20 > "If SET (or equivalently SET SESSION) is issued within a transaction = that is later aborted, the effects of the SET command disappear when the = transaction is rolled back. Once the surrounding transaction is = committed, the effects will persist until the end of the session, unless = overridden by another SET. >=20 > The effects of SET LOCAL last only till the end of the current = transaction, whether committed or not. A special case is SET followed by = SET LOCAL within a single transaction: the SET LOCAL value will be seen = until the end of the transaction, but afterwards (if the transaction is = committed) the SET value will take effect. It says transaction again here. > The effects of SET or SET LOCAL are also canceled by rolling back to a = savepoint that is earlier than the command. >=20 > If SET LOCAL is used within a function that has a SET option for the = same variable (see CREATE FUNCTION), the effects of the SET LOCAL = command disappear at function exit; that is, the value in effect when = the function was called is restored anyway. This allows SET LOCAL to be = used for dynamic or repeated changes of a parameter within a function, = while still having the convenience of using the SET option to save and = restore the caller's value. However, a regular SET command overrides any = surrounding function's SET option; its effects will persist unless = rolled back. > " I didn=E2=80=99t succeed in calling SET LOCAL TIMEZONE from within the = function. Could be I missed something, then Google (stackoverflow) = pointed me to set_config(). I did manage to apply it to the second function header, which I think = behaves such that the time zone change stays within function scope. = Right now I=E2=80=99m not 100% sure that I verified that. More to check = tomorrow. Frankly, I do hope that you=E2=80=99re right here, that would make my = work easier. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.