From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Piyush Newe <piyush(dot)newe(at)enterprisedb(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Rectifying wrong Date outputs |
Date: | 2011-09-05 23:32:27 |
Message-ID: | 201109052332.p85NWRn26034@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Piyush Newe wrote:
> Hi,
>
> I was randomly testing some date related stuff on PG & observed that the
> outputs were wrong.
>
> e.g.
> postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YY');
> to_date
> ------------
> 3910-01-01 <--------- Look at this
> (1 row)
>
> postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YYYY');
> to_date
> ------------
> 2010-01-01
> (1 row)
I have done some work on this problem, and have developed the attached
patch. It genarates the output in the final column of this table:
Oracle PostgreSQL With PG Patch
1 TO_DATE('01-jan-1', 'DD-MON-Y') 01-JAN-2011 01-JAN-2001 01-JAN-2001+
2 TO_DATE('01-jan-1', 'DD-MON-YY') 01-JAN-2001 01-JAN-2001 01-JAN-2001
3 TO_DATE('01-jan-1', 'DD-MON-YYY') 01-JAN-2001 01-JAN-2001 01-JAN-2001
4 TO_DATE('01-jan-1', 'DD-MON-YYYY') 01-JAN-0001 01-JAN-0001 01-JAN-0001
5 TO_DATE('01-jan-10', 'DD-MON-Y') Error 01-JAN-2010 01-JAN-2010
6 TO_DATE('01-jan-10', 'DD-MON-YY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
7 TO_DATE('01-jan-10', 'DD-MON-YYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
8 TO_DATE('01-jan-10', 'DD-MON-YYYY') 01-JAN-0010 01-JAN-0010 01-JAN-0010
9 TO_DATE('01-jan-067', 'DD-MON-Y') Error 01-JAN-2067 01-JAN-2067
10 TO_DATE('01-jan-111', 'DD-MON-YY') 01-JAN-0111 01-JAN-2011 01-JAN-2111*+
11 TO_DATE('01-jan-678', 'DD-MON-YYY') 01-JAN-2678 01-JAN-1678 01-JAN-1678+
12 TO_DATE('01-jan-001', 'DD-MON-YYYY') 01-JAN-0001 01-JAN-0001 01-JAN-0001
13 TO_DATE('01-jan-2010', 'DD-MON-Y') Error 01-JAN-4010 01-JAN-2010*
14 TO_DATE('01-jan-2010', 'DD-MON-YY') 01-JAN-2010 01-JAN-3910 01-JAN-2010*
15 TO_DATE('01-jan-2010', 'DD-MON-YYY') Error 01-JAN-3010 01-JAN-2010*
16 TO_DATE('01-jan-2010', 'DD-MON-YYYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
I marked with '*' every case where the patch doesn't match current PG,
and used a '+' to mark every case where it doesn't match Oracle.
I know Tom was worried that because the year field took more digits than
specified, it would prevent numeric columns from being pulled apart, but
our code has this check:
if (S_FM(node->suffix) || is_next_separator(node))
{
/*
* This node is in Fill Mode, or the next node is known to be a
* non-digit value, so we just slurp as many characters as we can get.
*/
errno = 0;
result = strtol(init, src, 10);
}
The reason these tests are accepting an unlimited number of digits is
because it is at the end of the string. If you place a digit field
right after it, it will not use more characters than specified:
test=> select to_date('9876', 'YYY');
to_date
------------
9876-01-01
(1 row)
test=> select to_date('9876', 'YYYMM');
to_date
------------
1987-06-01
(1 row)
Yes, not documented, but I assume the coder was trying to be helpful.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachment | Content-Type | Size |
---|---|---|
/rtmp/date_era.diff | text/x-diff | 2.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-09-05 23:33:09 | Re: Couple document fixes |
Previous Message | David Fetter | 2011-09-05 23:29:16 | Re: Couple document fixes |