Re: Rectifying wrong Date outputs

Applied, with a function rename.  The only odd case we have left is:
test=> select to_date('079', 'YYY');  to_date------------ 1979-01-01(1 row)

(Note the zero is ignored.)  I can't see an easy way to fix this and
continue to be easily documented.

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

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > 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
> 
> In an attempt to make the to_date/to_timestamp behavior documentable, I
> have modified the patch to have dates adjust toward the year 2020, and
> added code so if four digits are supplied, we don't do any adjustment. 
> Here is the current odd behavior, which is fixed by the patch:
> 
>     test=> select to_date('222', 'YYY');
>       to_date
>     ------------
>      2222-01-01
>     (1 row)
>     
>     test=> select to_date('0222', 'YYY');
>       to_date
>     ------------
>      2222-01-01
>     (1 row)
> 
> If they supply a full 4-digit year, it seems we should honor that, even
> for YYY.  YYYY still does no adjustment, and I doubt we want to change
> that:
> 
>     test=> select to_date('222', 'YYYY');
>       to_date
>     ------------
>      0222-01-01
>     (1 row)
>     
>     test=> select to_date('0222', 'YYYY');
>       to_date
>     ------------
>      0222-01-01
>     (1 row)
> 
> -- 
>   Bruce Momjian  <bruce@momjian.us>        http://momjian.us
>   EnterpriseDB                             http://enterprisedb.com
> 
>   + It's impossible for everything to be true. +

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> new file mode 100644
> index c03dd6c..282bb0d
> *** a/doc/src/sgml/func.sgml
> --- b/doc/src/sgml/func.sgml
> *************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
> *** 5550,5555 ****
> --- 5550,5564 ----
>   
>        <listitem>
>         <para>
> +        If the year format specification is less than four digits, e.g.
> +        <literal>YYY</>, and the supplied year is less than four digits,
> +        the year will be adjusted to be nearest to year 2020, e.g.
> +        <literal>95</> becomes 1995.
> +       </para>
> +      </listitem>
> + 
> +      <listitem>
> +       <para>
>          The <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
>          <type>date</type> has a restriction when processing years with more than 4 digits. You must
>          use some non-digit character or template after <literal>YYYY</literal>,
> diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
> new file mode 100644
> index 726a1f4..1a3ec1c
> *** a/src/backend/utils/adt/formatting.c
> --- b/src/backend/utils/adt/formatting.c
> *************** static void dump_node(FormatNode *node, 
> *** 964,969 ****
> --- 964,970 ----
>   
>   static char *get_th(char *num, int type);
>   static char *str_numth(char *dest, char *num, int type);
> + static int    add_era_to_partial_year(int year);
>   static int    strspace_len(char *str);
>   static int    strdigits_len(char *str);
>   static void from_char_set_mode(TmFromChar *tmfc, const FromCharDateMode mode);
> *************** is_next_separator(FormatNode *n)
> *** 1968,1973 ****
> --- 1969,1999 ----
>       return TRUE;                /* some non-digit input (separator) */
>   }
>   
> + 
> + static int
> + add_era_to_partial_year(int year)
> + {
> +     /*
> +      * Adjust all dates toward 2020;  this is effectively what happens
> +      * when we assume '70' is 1970 and '69' is 2069.
> +      */
> +     /* Force 0-69 into the 2000's */
> +     if (year < 70)
> +         return year + 2000;
> +     /* Force 70-99 into the 1900's */
> +     else if (year >= 70 && year < 100)
> +         return year + 1900;
> +     /* Force 100-519 into the 2000's */
> +     else if (year >= 100 && year < 519)
> +         return year + 2000;
> +     /* Force 520-999 into the 1000's */
> +     else if (year >= 520 && year < 1000)
> +         return year + 1000;
> +     else
> +         return year;
> + }
> + 
> + 
>   static int
>   strspace_len(char *str)
>   {
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2930,2972 ****
>                   break;
>               case DCH_YYY:
>               case DCH_IYY:
> !                 from_char_parse_int(&out->year, &s, n);
>                   out->yysz = 3;
> - 
> -                 /*
> -                  * 3-digit year: '100' ... '999' = 1100 ... 1999 '000' ...
> -                  * '099' = 2000 ... 2099
> -                  */
> -                 if (out->year >= 100)
> -                     out->year += 1000;
> -                 else
> -                     out->year += 2000;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_YY:
>               case DCH_IY:
> !                 from_char_parse_int(&out->year, &s, n);
>                   out->yysz = 2;
> - 
> -                 /*
> -                  * 2-digit year: '00' ... '69'    = 2000 ... 2069 '70' ... '99'
> -                  * = 1970 ... 1999
> -                  */
> -                 if (out->year < 70)
> -                     out->year += 2000;
> -                 else
> -                     out->year += 1900;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_Y:
>               case DCH_I:
> !                 from_char_parse_int(&out->year, &s, n);
>                   out->yysz = 1;
> - 
> -                 /*
> -                  * 1-digit year: always +2000
> -                  */
> -                 out->year += 2000;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_RM:
> --- 2956,2978 ----
>                   break;
>               case DCH_YYY:
>               case DCH_IYY:
> !                 if (from_char_parse_int(&out->year, &s, n) < 4)
> !                     out->year = add_era_to_partial_year(out->year);
>                   out->yysz = 3;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_YY:
>               case DCH_IY:
> !                 if (from_char_parse_int(&out->year, &s, n) < 4)
> !                     out->year = add_era_to_partial_year(out->year);
>                   out->yysz = 2;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_Y:
>               case DCH_I:
> !                 if (from_char_parse_int(&out->year, &s, n) < 4)
> !                     out->year = add_era_to_partial_year(out->year);
>                   out->yysz = 1;
>                   s += SKIP_THth(n->suffix);
>                   break;
>               case DCH_RM:

> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +