Re: BUG #4702: refusal to compute date_trunc('week', '0001-12-09 BC');

"Roman Kononov" <kononov@ftml.net> writes:
> test=# select date_trunc('week','0001-12-09 BC'::timestamp);
> ERROR:  cannot calculate week number without year information

Hmph, this code is pretty bletcherous.  Sleuthing in the CVS history, it
seems that the error check for "year zero" was put into isoweek2j (then
isoweek2date) at a time when only to_timestamp() used it.  It really
should never have been there at all; it's a syntax check for missing
fields in to_timestamp and has no business interfering with other uses
of the isoweek calculations.  What's more, it can't tell a valid
reference to year zero (a/k/a 1BC) from missing fields, which is the
proximate cause of the complained-of behavior.

It gets worse: it looks to me like the calling code was never right,
because it uses tmfc.year which is just the YY field, ignoring work
that we already did to derive the correct year accounting for CC and BC
fields.

I propose the attached patch ... comments?

            regards, tom lane

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.155
diff -c -r1.155 formatting.c
*** src/backend/utils/adt/formatting.c    12 Mar 2009 00:53:25 -0000    1.155
--- src/backend/utils/adt/formatting.c    13 Mar 2009 23:40:45 -0000
***************
*** 3281,3308 ****
           * be interpreted as a Gregorian day-of-year, or an ISO week date
           * day-of-year.
           */
          if (tmfc.mode == FROM_CHAR_DATE_ISOWEEK)
          {
              int            j0;        /* zeroth day of the ISO year, in Julian */

!             j0 = isoweek2j(tmfc.year, 1) - 1;

              j2date(j0 + tmfc.ddd, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
          }
          else
          {
!             int           *y,
!                         i;

!             int            ysum[2][13] = {
                  {31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365, 0},
                  {31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366, 0}};

-             if (!tm->tm_year)
-                 ereport(ERROR,
-                         (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
-                          errmsg("cannot calculate day of year without year information")));
-
              y = ysum[isleap(tm->tm_year)];

              for (i = 0; i <= 11; i++)
--- 3281,3309 ----
           * be interpreted as a Gregorian day-of-year, or an ISO week date
           * day-of-year.
           */
+
+         if (!tm->tm_year && !tmfc.bc)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+                      errmsg("cannot calculate day of year without year information")));
+
          if (tmfc.mode == FROM_CHAR_DATE_ISOWEEK)
          {
              int            j0;        /* zeroth day of the ISO year, in Julian */

!             j0 = isoweek2j(tm->tm_year, 1) - 1;

              j2date(j0 + tmfc.ddd, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
          }
          else
          {
!             const int  *y;
!             int            i;

!             static const int ysum[2][13] = {
                  {31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365, 0},
                  {31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366, 0}};

              y = ysum[isleap(tm->tm_year)];

              for (i = 0; i <= 11; i++)
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.196
diff -c -r1.196 timestamp.c
*** src/backend/utils/adt/timestamp.c    1 Jan 2009 17:23:50 -0000    1.196
--- src/backend/utils/adt/timestamp.c    13 Mar 2009 23:40:46 -0000
***************
*** 3668,3678 ****
      int            day0,
                  day4;

-     if (!year)
-         ereport(ERROR,
-                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-            errmsg("cannot calculate week number without year information")));
-
      /* fourth day of current year */
      day4 = date2j(year, 1, 4);

--- 3668,3673 ----