From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Alban Hertroijs <a.hertroijs@nieuwestroom.nl>
To: 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 08:48:27 -0800
Message-ID: <37804bda-ebe5-4786-be63-4d2a325368a6@aklaver.com> (raw)
In-Reply-To: <DB8P189MB104657B99989D8F61C5BE2C9E2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.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>
On 1/11/24 08:04, Alban Hertroijs wrote:
> > 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
> > 3). Both functions need to be created in the caching database
> before we can use them, while we have several environments where
> they would apply (DEV, pre-PROD, PROD).
>
> Would a function that dispatches its calls to a suitable array of
> hard-coded functions based on an IN parameter help any ?
>
> Karsten
>
> Well, probably, but we don't have many time zones that are relevant to
> us. For that, the current functions would be sufficient.
>
> 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)
"
I thought the issue there was maintaining the two Postgres functions?
> Provided that such a solution is possible, that is. If not, my current
> approach may have to suffice.
>
> The reason I decided to ask on the ML is that I'm finding it hard to
> believe that this transformation would be this difficult, so I expect
> that I must be missing something.
>
> Regards,
> Alban Hertroys
--
Adrian Klaver
adrian.klaver@aklaver.com
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: adrian.klaver@aklaver.com, a.hertroijs@nieuwestroom.nl, Karsten.Hilbert@gmx.net
Subject: Re: Time zone offset in to_char()
In-Reply-To: <37804bda-ebe5-4786-be63-4d2a325368a6@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>
* 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