RE: BUG #16027: Invalid output of to_timestamp

Thank you Tom for the quick response.

The problem actually manifests within the JDBC driver:

org.postgresql.util.PSQLException: Bad value for type timestamp/date/time: {1}

If what you are saying is that -05:32:11 is a perfectly valid offset from UTC, then this is a JDBC driver issue rather
thanpgsql, and if this is not the right bug list for JDBC reports I can take it there.
 

Thank you,

-Jeff

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, September 26, 2019 11:39 AM
To: Jeffrey Sturm <Jeff.Sturm@HelloWorld.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16027: Invalid output of to_timestamp

PG Bug reporting form <noreply@postgresql.org> writes:
> With a date in year 1904 or earlier, the to_timestamp function outputs
> unexpected characters:

> template1=# select to_timestamp('19041231','YYYYMMDD');
>          to_timestamp
> ------------------------------
>  1904-12-31 00:00:00-05:32:11

> Expected is "1904-12-31 00:00:00-06"

There's nothing particularly unexpected about that.
I surmise that you're using the America/Detroit zone, because nothing else matches that UTC offset, and what the IANA
timezonedatabase has for Detroit is
 

# ZoneNAMESTDOFFRULESFORMAT[UNTIL]
Zone America/Detroit-5:32:11 -LMT1905
-6:00-CST1915 May 15  2:00

with some commentary explaining why they think Detroit adopted standard time in 1905 rather than some other year.
Beforestandardized time, most places observed local solar time, so tzdb provides an estimate of what the UTC offset
wouldhave been for that in Detroit.
 

If this discombobulates you, you can edit your local copy of the tzdb files; but it's not wrong from our standpoint.

regards, tom lane
This email and any attachments transmitted with it are intended for use by the intended recipient(s) only. If you have
receivedthis email in error, please notify the sender immediately and then delete it. If you are not the intended
recipient,you must not keep, use, disclose, copy or distribute this email without the author’s prior permission. We
takeprecautions to minimize the risk of transmitting software viruses, but we advise you to perform your own virus
checkson any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses.
Theinformation contained in this communication may be confidential and may be subject to the attorney-client
privilege.