Re: [PATH] Correct negative/zero year in to_date/to_timestamp

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Feb 28, 2016 at 9:38 PM, Vitaly Burovoy
> <vitaly(dot)burovoy(at)gmail(dot)com> wrote:
>>> However, I'm not sure we ought to tinker with the behavior in this
>>> area. If YYYY-MM-DD is going to accept things that are not of the
>>> format YYYY-MM-DD, and I'd argue that -1-06-01 is not in that format,

>> It is not about format, it is about values.

> I disagree. In a format like "-1-06-01", you want the first minus to
> indicate negation and the other two to be a separator. That's not
> very far away from wanting the database to read your mind.

[ catches up with thread... ]

Yes. It would be more reasonable IMO for to_date to throw an error
because this is bad input. On the other hand, to_date mostly doesn't
throw an error no matter how bad the input is. I think that may have
been intentional, although its habit of producing garbage output
instead (and not, say, NULL) is certainly not very happy-making.

It's a bit schizophrenic for this patch to be both adding ereport's
for year zero (thereby breaking the no-failure-on-bad-input policy)
*and* trying to produce sane output for arguably-insane input.

I don't really see an argument why '0001-00-00' should be accepted
but '0000-01-01' should throw an error, but that would be the result
if we take this patch. And I quite agree with Robert that it's insane
to consider '-2-06-01' as satisfying the format 'YYYY-MM-DD'. The
fact that it even appears to do something related to a BC year is
an implementation artifact, and not a very nice one.

I would be in favor of a ground-up rewrite of to_date and friends, with
some better-stated principles (in particular, a rationale why they even
exist when date_in and friends usually do it better) and crisper error
detection. But I'm not seeing the argument that hacking at the margins
like this moves us forward on either point; what it does do is create
another backward-compatibility hazard for any such rewrite.

In short, I vote with Robert to reject this patch.

BTW, the context for the original report wasn't clear, but I wonder how
much of the actual problem could be addressed by teaching make_date()
and friends to accept negative year values as meaning BC.

regards, tom lane