Fix to_char(), to_date(), and to_timestamp() to handle negative/BC

century specifications just like positive/AD centuries.  Previously the
behavior was either wrong or inconsistent with positive/AD handling.

Centuries without years now always assume the first year of the century,
which is now documented.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ee42da80257dcb03d0a41d2f59222ed24011690b..d5ad4718b156b7bfce26f5047d62d7e4cce085e5 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5575,7 +5575,9 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
        if there is a <literal>YYY</literal>, <literal>YYYY</literal> or
        <literal>Y,YYY</literal> field. If <literal>CC</literal> is used with
        <literal>YY</literal> or <literal>Y</literal> then the year is computed
-       as <literal>(CC-1)*100+YY</literal>.
+       as the year in the specified century.  If the century is
+       specified but the year is not, the first year of the century
+       is assumed.
       </para>
      </listitem>
 

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 4347ad317faf29cd400e17e9b0e523f56021c464..ddb89c463bceb282c945eb19790bd3d902009515 100644 (file)
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -2640,8 +2640,15 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
            case DCH_CC:
                if (is_interval)    /* straight calculation */
                    i = tm->tm_year / 100;
-               else    /* century 21 starts in 2001 */
-                   i = (tm->tm_year - 1) / 100 + 1;
+               else
+               {
+                   if (tm->tm_year > 0)
+                       /* Century 20 == 1901 - 2000 */
+                       i = (tm->tm_year - 1) / 100 + 1;
+                   else
+                       /* Century 6BC == 600BC - 501BC */
+                       i = tm->tm_year / 100 - 1;
+               }
                if (i <= 99 && i >= -99)
                    sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2, i);
                else
@@ -3465,33 +3472,41 @@ do_to_timestamp(text *date_txt, text *fmt,
        /*
         * If CC and YY (or Y) are provided, use YY as 2 low-order digits for
         * the year in the given century.  Keep in mind that the 21st century
-        * runs from 2001-2100, not 2000-2099.
-        *
-        * If a 4-digit year is provided, we use that and ignore CC.
+        * AD runs from 2001-2100, not 2000-2099; 6th century BC runs from
+        * 600BC to 501BC.
         */
        if (tmfc.cc && tmfc.yysz <= 2)
        {
+           if (tmfc.bc)
+               tmfc.cc = -tmfc.cc;
            tm->tm_year = tmfc.year % 100;
            if (tm->tm_year)
-               tm->tm_year += (tmfc.cc - 1) * 100;
+           {
+               if (tmfc.cc >= 0)
+                   tm->tm_year += (tmfc.cc - 1) * 100;
+               else
+                   tm->tm_year = (tmfc.cc + 1) * 100 - tm->tm_year + 1;
+           }
            else
-               tm->tm_year = tmfc.cc * 100;
+               /* find century year for dates ending in "00" */
+               tm->tm_year = tmfc.cc * 100 + ((tmfc.cc >= 0) ? 0 : 1);         
        }
        else
+       /* If a 4-digit year is provided, we use that and ignore CC. */
+       {
            tm->tm_year = tmfc.year;
+           if (tmfc.bc && tm->tm_year > 0)
+               tm->tm_year = -(tm->tm_year - 1);
+       }
    }
-   else if (tmfc.cc)           /* use first year of century */
-       tm->tm_year = (tmfc.cc - 1) * 100 + 1;
-
-   if (tmfc.bc)
+   else if (tmfc.cc)   /* use first year of century */
    {
-       if (tm->tm_year > 0)
-           tm->tm_year = -(tm->tm_year - 1);
+       if (tmfc.bc)
+           tmfc.cc = -tmfc.cc;
+       if (tmfc.cc >= 0)
+           tm->tm_year = (tmfc.cc - 1) * 100 + 1;
        else
-           ereport(ERROR,
-                   (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
-                    errmsg("inconsistent use of year %04d and \"BC\"",
-                           tm->tm_year)));
+           tm->tm_year = tmfc.cc * 100 + 1;
    }
 
    if (tmfc.j)

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index ab8faab52e1cb7c513bb5c372604933cf5affd60..db2cfe6033320c89e6dbf25acf2fc1dd0ec79689 100644 (file)
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -992,7 +992,7 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
            | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
            | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
            | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
-           | 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
+           | 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
            | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
            | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
            | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
@@ -1063,7 +1063,7 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
            | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
            | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
            | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
-           | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
+           | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
            | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
            | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
            | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778

diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 9a4ce3e336343209b328e2e8e03eae081ef8b9cf..6581b5e6a9758252c74b6bd1e0f7de4cf89162db 100644 (file)
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1074,7 +1074,7 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
            | 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
            | 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
            | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
-           | 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
+           | 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
            | 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
            | 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
            | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
@@ -1146,7 +1146,7 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
            | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
            | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
            | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
-           | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
+           | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
            | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
            | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
            | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778