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 than pgsql, 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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, September 26, 2019 11:39 AM
To: Jeffrey Sturm <Jeff(dot)Sturm(at)HelloWorld(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16027: Invalid output of to_timestamp

PG Bug reporting form <noreply(at)postgresql(dot)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 timezone database 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. Before standardized time, most places observed local solar time, so tzdb provides an estimate of what the UTC offset would have 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 received this 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 take precautions to minimize the risk of transmitting software viruses, but we advise you to perform your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. The information contained in this communication may be confidential and may be subject to the attorney-client privilege.