Thread: BUG #17390: Function, to_date() -- unexpected values and a request

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.)

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.

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

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.)

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Pretend to_date doesn’t exist and just write a function that checks for
> valid inputs via RegEx and then parse it.  Maybe some day someone will
> develop a new conversion function that has considerably stricter, and
> self-defined, behavior (but given that to_date is basically “close enough
> to get the job done” I’m not optimistic).  Until then, protect yourself.

There are various things going on here:

* To the extent that to_date() has an accepted charter, it's "work
like the Oracle function of the same name".  Thus, arguments like
"it'd be better for my use-case if it did X" are basically not going
to find any traction.  Arguments like "Oracle's to_date() does X,
so we should too" have a better chance.  Unfortunately, most of us
lack access to an Oracle instance, making it hard to investigate
such details.

* The formatting.c code has recently been hacked up to also support
jsonpath datetime conversions, meaning that there's a second source
of truth involved; any proposed change would need to also be measured
against whether it moves us closer to or further away from the relevant
jsonpath standard.  That's another hard-to-get-hold-of reference, and
I'm not sure how detailed its answers would be anyway.

* There's also a fairly strong argument for maintaining backwards
compatibility, even if the existing behavior is arguably wrong per
the above points.

* formatting.c is an ill-documented, unmaintainable mess that nobody
is eager to touch.  (The original author is long gone.)

These things combine to encourage leaving it at the status quo.
Somebody who was really motivated and willing to get their hands
dirty could perhaps get something done, but I don't think any of
the current crop of hackers are very interested.

            regards, tom lane