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.klaver@aklaver.com