Re: Rectifying wrong Date outputs

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. +
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: