From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: smk_va@yahoo.com
Subject: BUG #17390: Function, to_date() -- unexpected values and a request
Date: Tue, 01 Feb 2022 00:01:40 +0000
Message-ID: <17390-3aea53f5c03c0e4a@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17390
Logged by: Murthy Kambhampaty
Email address: smk_va@yahoo.com
PostgreSQL version: 10.7
Operating system: Windows
Description:
The to_date() function seems not to fully test out-of-range values. An
example query and output are listed below:
postgres=> select to_date('1999-00-01', 'YYYY"-"MM"-"DD')
, to_date('1999.M00', 'YYYY".M"MM')
postgres-> ;
to_date | to_date
------------+------------
1999-01-01 | 1999-01-01
(1 row)
(also tested under with 13 and 14)
On the other hand, a too-high value for the month, such as 25, does result
in the expected out-of-range error. The above seems a bug.
The request has to do with processing of quarterly dates using the
'YYYY":Q"q' format string, and the like. The documentation presently notes
that, "In to_timestamp and to_date, weekday names or numbers (DAY, D, and
related field types) are accepted but are ignored for purposes of computing
the result. The same is true for quarter (Q) fields." First, it is easy to
miss this in the documentation. So a "CAUTION" notice, above the "Tip" would
be helpful; something to the effect that, "to_timestamp and to_date exist to
handle input formats that cannot be converted by simple casting. However,
certain input format specifiers are ignored, and thus the result may be
unexpected."
I submit, however, that returning '1999-01-01' in response to select
to_date('1999:Q3', 'YYYY":Q"q') is buggy -- the input is unambiguous in the
same sense that to_date('1999.M7', 'FXyyyy".M"mm') is, but the output is not
the same in both instances! Thus, the preferred approach here, IMHO, is to
return an error when the format string is ambiguous, and to return
consistent values when the format is unambiguous, with "consistent values"
observing the same rules as in make_date() and make_timestamp(). Thus, the
'DDD'-specifier in 'YYYY"-"DDD', would *not* be ignored, and the
'Q'-specifier in 'YYYY":Q"Q' would also not be ignored, whereas 'YYYY"-"DD'
and 'YYYY"-"D", and 'YYYY"-"WW' would return errors flagging the formats as
ambiguous or invalid. (It would also help to have clarity whether any format
specifiers other than 'yyyy' are case-insensitive.)
In my experience, monthly, quarterly, and weekly dates appear in government
statistics. (I have deployed PostgreSQL as part of a data-analysis stack
since version 7.2 or so, because I find it superior to many alternatives
because PostgreSQL returns errors when fed ambiguous or out-of-range data in
an ETL pipeline, rather than storing unexpected values; the above
inconsistencies in to_date() processing came as a mild shock.)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, smk_va@yahoo.com
Subject: Re: BUG #17390: Function, to_date() -- unexpected values and a request
In-Reply-To: <17390-3aea53f5c03c0e4a@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox