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 1rO0Uk-000NFf-Gp for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 19:13:39 +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 1rO0Uj-00EGiz-Ig for pgsql-general@arkaria.postgresql.org; Thu, 11 Jan 2024 19:13:37 +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 1rO0Uj-00EGhq-83 for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 19:13:37 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rO0Ug-0013uL-VD for pgsql-general@lists.postgresql.org; Thu, 11 Jan 2024 19:13:36 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-a2c29418ad5so177165266b.0 for ; Thu, 11 Jan 2024 11:13:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705000414; x=1705605214; 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=7ZoRKNSNyGo/pf3KKNr2pmqy8PkEBm0NM4tDjIapjLo=; b=RWX8I6IEQu/gf/K5zkYBpVBk3gEInYLxamkaj0nybnrzCyfDWCaotBuTK/N6d4wx7q pNtSkzrF1kG6xG2ySyuaJfKHNSPIFhqhmnHLBTvb7Hvz7L38/aBIEmj6iFF93ZLXyPIO mInaA7PIgBuAM2LiP3oRFOCMkX/+ucq2TWAnDvwnL9XEIhESB0t84qpjtmLCXyOlROTj utNRC1kJ/kYOQQuuY3b0sAqFuUU/FdKOZoLEfgffwuJ6XzMJ3GyrC8teeOJGifQCIRpr Nona29UP3MZcdS7AHcj8K19wW+03YZ9UspzZjkuMs/Ny3HAAmLayrcONtv8/D/7ObOkj Hf8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705000414; x=1705605214; 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=7ZoRKNSNyGo/pf3KKNr2pmqy8PkEBm0NM4tDjIapjLo=; b=L4tzihOoIbM9f7XQMLZXVordHux5rxqyZ4eKDgW4R9an62+pugBj8WRUjUA43Syai/ hAf++heDIK2z+ZtyJ6fwFSs7ds+xlqFOkCxMXhvIqcG5FV0p+8iJ9xvo/yMxSqtxSidZ zfmNUXCV7mOGUzIz5P7/RVI/m95PfsNgNJ2K/gNVymRteJj7XBjW370GgNpds8H3iQPI BTZt81QbWYe7fD2h89eODcNEM4IwYCM9Fkp+NwUJdBMMKhv3/LXMQP+FPjuQFY/Ik8ia rlTEmFYB1nXaQUT9Gx0kvDy0wat1bBGkYffBYSwYqx+mKqNv6rFNg0TDarp05+7jCxoL wLVg== X-Gm-Message-State: AOJu0YzmuzSm37vlfm69jvyN368ygbnjYaPxYUiaWZ0ubb7CMO8KAgXW dLIwJwDsowxqapMPJAhCUcM= X-Google-Smtp-Source: AGHT+IERuwW20RslWzTUsoZeGRCuLigpZSrtk7EAC+jW8oWewheI9QgDOdQfZjPEO6Arwlula+3k9g== X-Received: by 2002:a17:906:3cf2:b0:a2c:539:efef with SMTP id d18-20020a1709063cf200b00a2c0539efefmr57750ejh.101.1705000413975; Thu, 11 Jan 2024 11:13:33 -0800 (PST) Received: from smtpclient.apple (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id u3-20020a1709064ac300b00a269b4692a9sm904963ejt.84.2024.01.11.11.13.33 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 11 Jan 2024 11:13:33 -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: <5fb4d1a8-0597-4f87-9a6b-db1a99b0ebfe@aklaver.com> Date: Thu, 11 Jan 2024 20:13:23 +0100 Cc: Alban Hertroys , Karsten Hilbert , "pgsql-general@lists.postgresql.org" Content-Transfer-Encoding: quoted-printable Message-Id: <520EC025-CFF0-4558-BA24-35B141A3818D@gmail.com> References: <37804bda-ebe5-4786-be63-4d2a325368a6@aklaver.com> <5fb4d1a8-0597-4f87-9a6b-db1a99b0ebfe@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 18:27, Adrian Klaver = wrote: >=20 > On 1/11/24 08:48, Adrian Klaver wrote: >> On 1/11/24 08:04, Alban Hertroijs wrote: >=20 >>> The drawback, as mentioned, being that we need to maintain those = functions in each deployment, which is a bit of a hassle (albeit a minor = one) because we need to customise both the TDV side and the PostgreSQL = side in that case. Our preferred solution would be to just add a few = entries to the TDV database-specific capabilities file (as described in = my initial message) >> Are you referring to?: >> "It currently have this: >> ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1) >> ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2) >> " >=20 > It finally dawned on me, you want to replace the user defined = functions above with Postgres builtins only. Try as I might I could not = come with that solution. Exactly. I was having the same problem of finding a solution, quite to = my surprise. >> I thought the issue there was maintaining the two Postgres functions? Yup, those two functions in fact. There will be at least 3 separate deployments, while maintenance of the = database(-schema) contents is the responsibility of the 3rd party = application (TDV). PG is used as a caching DB here, we therefore intend = to treat the data in it as volatile; it shouldn=E2=80=99t hurt if we = decide to recreate the caches from scratch from source data. Having = custom code in there not under control of the 3rd party application = breaks that guideline. If they=E2=80=99re necessary, then so be it, but I can=E2=80=99t shake = the feeling that we can achieve this without custom code in the = database. Regards, Alban Hertroys -- There is always an exception to always.