Re: Time zone offset in to_char()

> On 11 Jan 2024, at 17:43, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 1/11/24 07:06, Alban Hertroijs wrote:
>> Hi all,
>
>> In the above, I worked around the issue using a couple of user-defined functions in PG. That should give a
reasonableidea 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
isolatedto 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
zoneis now hardcoded into the function definition, while 
>
> I don't think the set_config and SET are acting the way you think they are:
>
> set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET)
>
> "
> set_config ( setting_name text, new_value text, is_local boolean ) → text
>
> Sets the parameter setting_name to new_value, and returns that value. If is_local is true, the new value will only
applyduring the current transaction. If you want the new value to apply for the rest of the current session, use false
instead.This function corresponds to the SQL command SET. 
>
> set_config('log_statement_stats', 'off', false) → off"
> "

I tried this like so:

select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, ‘YYYY-MM-DD HH24:MI:SS.SU0
TZH:TZM’).

The result of the second call was based on time zone ‘Europe/Amsterdam’, where it wasn’t when called outside the
transaction(when it was based on UTC corresponding to the server time zone). 
So the time zone set with set_config(…, …, true) appeared to leak out of function scope and applied to transaction
scope(as described in the quoted text). 
For brevity I could run that query tomorrow when I’m back at work.

> SET(https://www.postgresql.org/docs/current/sql-set.html)
>
> "If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET
commanddisappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will
persistuntil the end of the session, unless overridden by another SET. 
>
> The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case
isSET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the
transaction,but afterwards (if the transaction is committed) the SET value will take effect. 

It says transaction again here.

> The effects of SET or SET LOCAL are also canceled by rolling back to a savepoint that is earlier than the command.
>
> If SET LOCAL is used within a function that has a SET option for the same variable (see CREATE FUNCTION), the effects
ofthe SET LOCAL command disappear at function exit; that is, the value in effect when the function was called is
restoredanyway. This allows SET LOCAL to be used for dynamic or repeated changes of a parameter within a function,
whilestill having the convenience of using the SET option to save and restore the caller's value. However, a regular
SETcommand overrides any surrounding function's SET option; its effects will persist unless rolled back. 
> "

I didn’t succeed in calling SET LOCAL TIMEZONE from within the function. Could be I missed something, then Google
(stackoverflow)pointed me to set_config(). 

I did manage to apply it to the second function header, which I think behaves such that the time zone change stays
withinfunction scope. Right now I’m not 100% sure that I verified that. More to check tomorrow. 

Frankly, I do hope that you’re right here, that would make my work easier.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.