Re: Time zone offset in to_char()

[Alban Hertroijs <a.hertroijs@xxxxxxxxxxxxxxx>]

> 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 apply during 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.

Following up on my own mail from yesterday evening, here's the output that shows the function using set_config 'leaking' the timezone change to outside the function (the first select vs. the 2^nd select) into the (same) transaction, whereas the function with the time zone bound to the header does not (the 3^rd select).

These are all from a single session, ran consecutively - a straight copy-paste from psql.

ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');

          todatetimeoffset          |              to_char

------------------------------------+------------------------------------

 2024-01-12 09:41:44.7019350 +01:00 | 2024-01-12 09:41:44.7019350 +01:00

(1 row)

ciscache=> select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');

              to_char

------------------------------------

 2024-01-12 08:41:54.4739800 +00:00

(1 row)

ciscache=> select ToDatetimeOffsetNL(current_timestamp), to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');

         todatetimeoffsetnl         |              to_char

------------------------------------+------------------------------------

 2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00

(1 row)

Regards,

Alban Hertroijs.