From: | Jim Nasby <jim(dot)nasby(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Alban Hertroijs <a(dot)hertroijs(at)nieuwestroom(dot)nl>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Time zone offset in to_char() |
Date: | 2024-01-12 00:26:52 |
Message-ID: | bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/11/24 6:20 PM, Jim Nasby wrote:
> On 1/11/24 5:53 PM, Tom Lane wrote:
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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)
NEVERMIND... I see now that doesn't actually work.
Perhaps we should add a variant of timezone() that handles this use-case...
--
Jim Nasby, Data Architect, Austin TX
From | Date | Subject | |
---|---|---|---|
Next Message | Кристина Валентей | 2024-01-12 06:19:52 | Software Bill of Materials (SBOM) |
Previous Message | Jim Nasby | 2024-01-12 00:20:59 | Re: Time zone offset in to_char() |