Re: Support TZ format code in to_timestamp()

On 6/21/23 20:07, Bruce Momjian wrote:
> On Tue, Jun 13, 2023 at 12:20:42PM -0400, Tom Lane wrote:
>> It's annoyed me for some time that to_timestamp() doesn't implement
>> the TZ format code that to_char() has. I finally got motivated to
>> do something about that after the complaint at [1] that jsonpath's
>> datetime() method can't read typical JSON.stringify() output like
>> "2023-05-22T03:09:37.825Z". We do already understand "Z" as a
>> time zone abbreviation for UTC; we just need to get formatting.c
>> to support this.
>
> I have to admit I tend to prefer actual time zone names like
> "America/New_York" over acronyms or offset values. However, I can see
> the dump/restore problem with such names.

I think the abbreviations are worse than useless -- dangerously
misleading even. I was converting a timestamp I had pulled from the
internet the other day in IST (India Standard Time) using Postres to
test some new code I was working on. I got a rather surprising result so
changed it to Asia/Kolkata and got what I expected.

Turns out IST is *also* Israel Standard Time and Irish Standard Time. I
think Postres gave me the conversion in Irish time. At any rate, it was
not offset by 30 minutes which was the dead giveaway.

Offsets are fine when you just need an absolute date to feed into
something like recovery and it doesn't much matter what timezone you
were in.

Z and UTC also seem fine since they are unambiguous as far as I know.

Regards,
-David