Re: Rectifying wrong Date outputs

On Mon, Mar 21, 2011 at 6:24 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> Having said that, it's not entirely clear to me what sane behavior is
>>> here.  Personally I would expect that an n-Ys format spec would consume
>>> at most n digits from the input.  Otherwise how are you going to use
>>> to_date to pick apart strings that don't have any separators?
>
> Yeah, seems reasonable.

On the flip side, what if you want to allow either a two digit year or
a four digit year? It doesn't seem unreasonable to allow YY to
emcompass what YYYY would have allowed, unless there's a separate
notion for 'either YY or YYYY'.

> It makes sense to me. Year "1", when dat format is "Y", means the year
> closest to current date that ends with 1. Or maybe the year that ends with 1
> in the current decade. This is analoguous to how two-digit years are
> interpreted (except that we've hardcoded that the "current date" to compare
> against is year 2000 - an assumption that will start to bite us some time
> before year 2100).

Agree with all of this.

> So ignoring the cases where Oracle throws an error but PostgreSQL doesn't,
> there's four cases where the results differ:
>
>> *Data Format Oracle PostgreSQL EDBAS*
>> TO_DATE('01-jan-1',  'DD-MON-Y') 01-JAN-2011 01-JAN-2001 01-JAN-2001
>> TO_DATE('01-jan-111',  'DD-MON-YY') 01-JAN-0111 01-JAN-2011 Error
>> TO_DATE('01-jan-678',  'DD-MON-YYY') 01-JAN-2678 01-JAN-1678 01-JAN-2678
>> TO_DATE('01-jan-2010',  'DD-MON-YY') 01-JAN-2010 01-JAN-3910 Error
>
> IMHO our current behavior in 2nd and 4th case is so bizarre that we should
> change them to match Oracle. I think we should fix the 1st too, the notion
> that a single-digit year means something between 2000-2009 seems pretty
> useless (granted, using a single digit for year is brain-dead to begin
> with).

I agree, but do we understand what Oracle does categorically, rather
than just its output on this specific input?

> The 3rd one is debatable. The range for three-digit years is currently
> 1100-2099, which is enough range for many applications. But should we change
> it for the sake of matching Oracle's behavior? Not that anyone uses YYY in
> practice, but still.

I'm OK with that, but again, exactly what rule is Oracle applying here?

> BTW, whatever behavior we choose, this needs to be documented. I don't see
> anything in the docs on how Y, YY or YYY are expanded.

+1.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company