Re: Time zone offset in to_char()

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(dot)klaver(at)aklaver(dot)com