Обсуждение: BUG #16027: Invalid output of to_timestamp
The following bug has been logged on the website:
Bug reference: 16027
Logged by: Jeff Sturm
Email address: jeff.sturm@helloworld.com
PostgreSQL version: Unsupported/Unknown
Operating system: CentOS 6.10
Description:
Actual version is 8.4.20.
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"
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 timezone database has for Detroit is
# Zone NAME STDOFF RULES FORMAT [UNTIL]
Zone America/Detroit -5:32:11 - LMT 1905
-6:00 - CST 1915 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
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.
Jeffrey Sturm <Jeff.Sturm@HelloWorld.com> writes:
> 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
ratherthan pgsql, and if this is not the right bug list for JDBC reports I can take it there.
Well, it's a valid offset so far as Postgres is concerned. Some poking
around suggests that relevant standards such as ISO 8601 only admit UTC
offsets specified to the nearest minute --- but tzdb has some entries
specified to seconds, so that's what we follow.
regards, tom lane