Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)

On 2/17/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Bruce Momjian escribió:
>
> > Maybe now would be an appropriate time to discuss the open questions in
> > the submitting email:
>
> > > Brendan Jurd wrote:
> > > > I'd also like to raise the topic of how conversion from text to ISO
> > > > week dates should be handled, where the user has specified a bogus
> > > > mixture of fields. Existing code basically ignores these issues; for
> > > > example, if a user were to call to_date('1998-01-01 2454050',
> > > > 'YYYY-MM-DD J') the function returns 2006-01-01, a result of setting
> > > > the year field from YYYY, then overwriting year, month and day with
> > > > the values from the Julian date in J, then setting the month and day
> > > > normally from MM and DD.
> > > >
> > > > 2006-01-01 is not a valid representation of either of the values the
> > > > user specified. Now you might say "ask a silly question, get a silly
> > > > answer"; the user shouldn't send nonsense arguments to to_date and
> > > > expect a sensible result. But perhaps the right way to respond to a
> > > > broken timestamp definition is to throw an error, rather than behave
> > > > as though everything has gone to plan, and return something which is
> > > > not correct.
> > > >
> > > > The same situation can arise if the user mixes ISO and Gregorian data;
> > > > how should Postgres deal with something like to_date('2006-250',
> > > > 'IYYY-DDD')? The current behaviour in my patch is actually to assume
> > > > that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
> > > > of the ISO year 2006" is total gibberish. But perhaps it should be
> > > > throwing an error message.
>
> My thinking is that erroneous patterns should throw an error, and not
> try to second-guess the user. (IIRC this was being discussed in some
> other thread not long ago).

It seems to me there are basically two different responses to the
problem of invalid patterns. One is to reject all patterns which
potentially under- or over-constrain the date value, and the other is
to only reject those patterns which, when applied to the given date
string, actually cause a conflict.

For example, on the surface the pattern 'YYYY-MM-DD J' would appear to
be invalid, because it specifies the date using both the Gregorian and
Julian conventions. You could argue that the whole idea of using a
pattern like this is bogus, and reject the pattern as soon as it is
parsed.

On the other hand, if a user called to_date('2007-02-17 2454149',
'YYYY-MM-DD J'), and you attempted to resolve the pattern you would
find that the Julian date and the Gregorian date agree perfectly with
each other, and there is no reason to reject the conversion.

My gut reaction at first was to go with the former approach. It's
programmatically more simple, and it's easier to explain in
documentation/error messages. But then it occurred to me that one of
the use cases for to_date is slurping date information out of textual
reports which may contain redundant date information. If a user
wanted to parse something like "2007-02-17 Q1", he would probably try
'YYYY-MM-DD "Q"Q', even though this pattern is logically
over-constraining. Would it be fair to throw an error in such a case?

Please let me know what you think.

BJ