Re: Time zone offset in to_char()

Alban Hertroijs wrote:

> 1). The first function has as a drawback that it changes the time zone for
> the entire transaction (not sufficiently isolated to my tastes)

But if you add in the function declaration
SET timezone TO 'Europe/Amsterdam'
like in your 2nd function, or simply
SET timezone FROM CURRENT
doesn't that solve this problem?

Because as the doc says

If a SET clause is attached to a function, then the effects of a SET
LOCAL command executed inside the function for the same variable are
restricted to the function: the configuration parameter's prior
value is still restored at function exit

The actual value to which the timezone is set through this statement
does not matter, as the first instruction in the function overwrites
it:
   perform set_config('timezone', tz_, true /* local */);

The point is that the prior value of timezone being restored
automatically at function exit, the temporary setting will not
leak out of the function.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite