Re: Time zone offset in to_char()

On 1/11/24 5:53 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', 'YYYY-MM-DD
>> HH24:MI:SS.US0 TZH:TZM') ;
>>                 to_char
>> ------------------------------------
>>    2024-01-12 00:44:57.5421420 +00:00
>> (1 row)
> 
>> You end up with string that does not the correct offset as the AT TIME
>> ZONE outputs a timestamp not timestamptz value.
> 
> Yeah.  to_char() does not have any source for the TZ/TZH/TZM fields
> other than the prevailing value of the timezone parameter, so you
> really have to set that the way you want if you desire to use these
> format fields.  As noted upthread, SET LOCAL together with a (dummy)
> "SET timezone" clause in the function definition can be used to get
> the effect of a function-local setting of the parameter.  I don't
> know of another way to achieve that result above the C-code level.
> 
>             regards, tom lane

Sorry, I was implying that you could use the generated timestamp without 
timezone as a string and supply the necessary timezone:

select to_char(timestamptz(timezone('UTC',tstz) || ' CST6CDT'), 
'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM') from tstz ;
               to_char
------------------------------------
  2024-01-11 23:29:00.0493300 -06:00
(1 row)

-- 
Jim Nasby, Data Architect, Austin TX