Re: Rectifying wrong Date outputs

On Wed, Mar 16, 2011 at 8:21 AM, Piyush Newe
<piyush(dot)newe(at)enterprisedb(dot)com> wrote:
> Data Format                 PostgreSQL EDBAS
> TO_DATE('01-jan-10',  'DD-MON-Y')         2010-01-01 Error
> TO_DATE('01-jan-10',  'DD-MON-YY')         2010-01-01 01-JAN-2010
> TO_DATE('01-jan-10',  'DD-MON-YYY') 2010-01-01 01-JAN-2010
> TO_DATE('01-jan-10',  'DD-MON-YYYY') 0010-01-01 01-JAN-0010
> In this case, it seems in last 3 cases PG is behaving correctly. Whereas in
> 1st case the output is not correct since the Format ('Y') is lesser than the
> actual input ('10'). But PG is ignoring this condition and throwing whatever
> is input. The output year is might not be the year, what user is expecting.
> Hence PG should throw an error.

I can't get worked up about this. If there's a consensus that
throwing an error here is better, fine, but on first blush the PG
behavior doesn't look unreasonable to me.

> Data Format                 PostgreSQL EDBAS
> TO_DATE('01-jan-2010', 'DD-MON-Y') 4010-01-01 Error
> TO_DATE('01-jan-2010', 'DD-MON-YY') 3910-01-01 Error
> TO_DATE('01-jan-2010', 'DD-MON-YYY') 3010-01-01 Error
> TO_DATE('01-jan-2010', 'DD-MON-YYYY') 2010-01-01 01-JAN-2010

These cases look a lot stranger. I'm inclined to think that if the
number of digits specified exceeds the number of Ys, then we can
either (1) throw an error, as you suggest or (2) give the same answer
we would have given if the number of Ys were equal to the number of
digits given. In other words, if we're not going to error out here,
all of these should return 2010-01-01.

> Data Format PostgreSQL EDBAS
> TO_DATE('01-jan-067', 'DD-MON-Y') 2067-01-01 Error
> TO_DATE('01-jan-111', 'DD-MON-YY') 2011-01-01 Error
> TO_DATE('01-jan-678', 'DD-MON-YYY') 1678-01-01 01-JAN-2678
> TO_DATE('01-jan-001', 'DD-MON-YYYY') 0001-01-01 01-JAN-0001

These are so strange that it's hard to reason about them; who uses
three-digit years? In the third case above, you should EDBAS
deciding that 678 means 2678 instead of 1678, but that seems quite
arbitrary. 1678 seems just as plausible. But the behavior in the
second case looks wrong (shouldn't the answer should be either 1111 or
2111?) and the first case looks inconsistent with the third one (why
does 067 mean 2067 rather than 1967 while 678 means 1678 rather than
2678?).

I'm inclined to think that we have a bug here in the case where the #
of digits given is greater than the # of Ys. See also this:

rhaas=# select to_date('01-jan-678', 'DD-MON-Y');
to_date
------------
2678-01-01
(1 row)

rhaas=# select to_date('01-jan-678', 'DD-MON-YY');
to_date
------------
2578-01-01
(1 row)

rhaas=# select to_date('01-jan-678', 'DD-MON-YYY');
to_date
------------
1678-01-01
(1 row)

It's a lot less clear to me that we have a bug in the other direction
(# of digits given is less than the # of Ys), but maybe....

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