Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates) - Mailing list pgsql-hackers

Because this patch was not completed, I have added it to the TODO list:

* Fix to_date()-related functions to consistently issue errors
 http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php


---------------------------------------------------------------------------

Brendan Jurd wrote:
> On 2/17/07, Alvaro Herrera <alvherre@commandprompt.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

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +