From: Alban Hertroys <haramrae@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Alban Hertroys <a.hertroijs@nieuwestroom.nl>
Cc: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Time zone offset in to_char()
Date: Thu, 11 Jan 2024 20:13:23 +0100
Message-ID: <520EC025-CFF0-4558-BA24-35B141A3818D@gmail.com> (raw)
In-Reply-To: <5fb4d1a8-0597-4f87-9a6b-db1a99b0ebfe@aklaver.com>
References: <DB8P189MB1046C0D82500690791CED07AE2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM>
<trinity-d7c2c180-6711-4cd5-86a0-60c7dad52e88-1704985919018@3c-app-gmx-bs12>
<DB8P189MB104657B99989D8F61C5BE2C9E2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM>
<37804bda-ebe5-4786-be63-4d2a325368a6@aklaver.com>
<5fb4d1a8-0597-4f87-9a6b-db1a99b0ebfe@aklaver.com>
> On 11 Jan 2024, at 18:27, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 1/11/24 08:48, Adrian Klaver wrote:
>> On 1/11/24 08:04, Alban Hertroijs wrote:
>
>>> 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)
>> "
>
> 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’t 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’re necessary, then so be it, but I can’t shake the feeling that we can achieve this without custom code in the database.
Regards,
Alban Hertroys
--
There is always an exception to always.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: haramrae@gmail.com, adrian.klaver@aklaver.com, a.hertroijs@nieuwestroom.nl, Karsten.Hilbert@gmx.net
Subject: Re: Time zone offset in to_char()
In-Reply-To: <520EC025-CFF0-4558-BA24-35B141A3818D@gmail.com>
References: <DB8P189MB1046C0D82500690791CED07AE2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM> <trinity-d7c2c180-6711-4cd5-86a0-60c7dad52e88-1704985919018@3c-app-gmx-bs12> <DB8P189MB104657B99989D8F61C5BE2C9E2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM> <37804bda-ebe5-4786-be63-4d2a325368a6@aklaver.com> <5fb4d1a8-0597-4f87-9a6b-db1a99b0ebfe@aklaver.com> <520EC025-CFF0-4558-BA24-35B141A3818D@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox