From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Paquier <michael@paquier.xyz>
cc: Aleksander Alekseev <aleksander@timescale.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: TimestampTz->Text->TimestampTz casting fails with DateStyle
 'Postgres'
In-reply-to: <Z1eKmoJHXVd8_oz1@paquier.xyz>
References: 
 <CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com>
 <3416600.1730987836@sss.pgh.pa.us>
 <CAJ7c6TMzJDLutYt+pcm1Jt+sDSRHjvjezM9o8heEoo_LwgP75A@mail.gmail.com>
 <CAJ7c6TMpBUMLqep4ycxySN+SFsf-eG2v-nUaVSzu2jSd5eDMAA@mail.gmail.com>
 <Z1eKmoJHXVd8_oz1@paquier.xyz>
Comments: In-reply-to Michael Paquier <michael@paquier.xyz>
	message dated "Tue, 10 Dec 2024 09:26:02 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4167038.1733796752.1@sss.pgh.pa.us>
Date: Mon, 09 Dec 2024 21:12:32 -0500
Message-ID: <4167039.1733796752@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4167039.1733796752%40sss.pgh.pa.us>
Precedence: bulk

Michael Paquier <michael@paquier.xyz> writes:
> FWIW, I have a hard time understanding why ignoring "LMT", which is
> a timezone abbreviation meaning "Local Mean Time", in datetktbl[],
> which is a table for date/time keywords, is the right thing to do.

I agree.  We should be mapping it to whatever GMT offset "LMT" means
in the selected zone (assuming there is an entry, which it seems like
there is in most of them).  I've not gotten around to looking at what
that would take, but it's probably not entirely trivial, since it
would mean different GMT offsets in different zones.

One thing that might be interesting to look at is whether the same
mechanism could be used for other TZ abbreviations defined by the tzdb
data, instead of relying on our hard-wired lists.

> Worth noting that we may want to do something in ECPG's dt_common.c as
> well, that holds a similar table with TZ abbreviation data and LMT is
> missing there?

ECPG's datetime support is so far behind the server's that it's kind
of sad.  But bringing it up to speed would be a large investment of
effort, something I for one am not prepared to put into it.  I sure
don't think we should hold up fixing this on the server side pending
making that happen.

			regards, tom lane