pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim Nasby <jim.nasby@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Alban Hertroijs <a.hertroijs@nieuwestroom.nl>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Time zone offset in to_char()
Date: Thu, 11 Jan 2024 18:26:52 -0600
Message-ID: <bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com> (raw)
In-Reply-To: <8ad4100f-42d6-46d7-a7f1-17ac5864be7d@gmail.com>
References: <DB8P189MB1046C0D82500690791CED07AE2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM>
	<cc4d16e8-3263-41e1-9a82-4410c0bdd347@gmail.com>
	<f4fc9c8d-c8fb-4da2-a0f6-ace98cbc7b40@aklaver.com>
	<341384.1705017200@sss.pgh.pa.us>
	<8ad4100f-42d6-46d7-a7f1-17ac5864be7d@gmail.com>

On 1/11/24 6:20 PM, Jim Nasby wrote:
> 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)

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






view thread (21+ messages)  latest in thread

Message-ID: <bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com>
Permalink:  ../bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com/
Also on:    postgresql.org/message-id/bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: jim.nasby@gmail.com, tgl@sss.pgh.pa.us, adrian.klaver@aklaver.com, a.hertroijs@nieuwestroom.nl
  Subject: Re: Time zone offset in to_char()
  In-Reply-To: <bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com>
  References: <DB8P189MB1046C0D82500690791CED07AE2682@DB8P189MB1046.EURP189.PROD.OUTLOOK.COM> <cc4d16e8-3263-41e1-9a82-4410c0bdd347@gmail.com> <f4fc9c8d-c8fb-4da2-a0f6-ace98cbc7b40@aklaver.com> <341384.1705017200@sss.pgh.pa.us> <8ad4100f-42d6-46d7-a7f1-17ac5864be7d@gmail.com> <bf2cda06-d2c8-4bea-b6c1-03d2436fafff@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox