From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> |
Cc: | Piyush Newe <piyush(dot)newe(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Rectifying wrong Date outputs |
Date: | 2011-03-21 13:39:41 |
Message-ID: | AANLkTiny0De9Sx-P72HOcPjz=zWMYg6hh4QuLa-AwSo0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-03-21 13:57:57 | Re: Rectifying wrong Date outputs |
Previous Message | Robert Haas | 2011-03-21 13:32:31 | Re: Planner regression in 9.1: min(x) cannot use partial index with NOT NULL |