Re: [HACKERS] date_part() BUG?

> > using age() function I get: @ 1 hour 13 mins 27.88 secs
> > using date_part() over age() I get:
> <snip tests for years through seconds>
> > But
> > 1 for 'decade'     NOT OK!
> > 1 for 'century'    NOT OK!
> > 1 for 'millenium'    NOT OK!
> I can see that here. Will look at it. Thanks for the report...

Sorry, it was a cut-and-paste error, with an explicit "+ 1" where it
shouldn't be. Patch enclosed, which includes another recent fix for date
input having mixed US/Euro-style formats and text months. If you've
already applied that one, then strip it out of the patch before applying
(or tell patch the right thing when it complains).

Thanks again for the report.

                    - Tom*** dt.c.orig    Sat Jan 23 03:12:23 1999
--- dt.c    Thu Feb 11 17:30:23 1999
***************
*** 249,255 ****
          case DTK_DELTA:
              if (tm2timespan(tm, fsec, span) != 0)
              {
! #if FALSE
                  TIMESPAN_INVALID(span);
  #endif
                  elog(ERROR, "Bad timespan external representation '%s'", str);
--- 249,255 ----
          case DTK_DELTA:
              if (tm2timespan(tm, fsec, span) != 0)
              {
! #if NOT_USED
                  TIMESPAN_INVALID(span);
  #endif
                  elog(ERROR, "Bad timespan external representation '%s'", str);
***************
*** 1412,1418 ****

      if (DATETIME_NOT_FINITE(*datetime))
      {
! #if FALSE
  /* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
          elog(ERROR, "Datetime is not finite", NULL);
  #endif
--- 1412,1418 ----

      if (DATETIME_NOT_FINITE(*datetime))
      {
! #if NOT_USED
  /* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
          elog(ERROR, "Datetime is not finite", NULL);
  #endif
***************
*** 1494,1500 ****
              if (tm2datetime(tm, fsec, &tz, result) != 0)
                  elog(ERROR, "Unable to truncate datetime to '%s'", lowunits);

! #if FALSE
          }
          else if ((type == RESERV) && (val == DTK_EPOCH))
          {
--- 1494,1500 ----
              if (tm2datetime(tm, fsec, &tz, result) != 0)
                  elog(ERROR, "Unable to truncate datetime to '%s'", lowunits);

! #if NOT_USED
          }
          else if ((type == RESERV) && (val == DTK_EPOCH))
          {
***************
*** 1552,1558 ****

      if (TIMESPAN_IS_INVALID(*timespan))
      {
! #if FALSE
          elog(ERROR, "Timespan is not finite", NULL);
  #endif
          result = NULL;
--- 1552,1558 ----

      if (TIMESPAN_IS_INVALID(*timespan))
      {
! #if NOT_USED
          elog(ERROR, "Timespan is not finite", NULL);
  #endif
          result = NULL;
***************
*** 1610,1616 ****
              result = NULL;
          }

! #if FALSE
      }
      else if ((type == RESERV) && (val == DTK_EPOCH))
      {
--- 1610,1616 ----
              result = NULL;
          }

! #if NOT_USED
      }
      else if ((type == RESERV) && (val == DTK_EPOCH))
      {
***************
*** 1678,1684 ****

      if (DATETIME_NOT_FINITE(*datetime))
      {
! #if FALSE
  /* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
          elog(ERROR, "Datetime is not finite", NULL);
  #endif
--- 1678,1684 ----

      if (DATETIME_NOT_FINITE(*datetime))
      {
! #if NOT_USED
  /* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
          elog(ERROR, "Datetime is not finite", NULL);
  #endif
***************
*** 1843,1849 ****

      if (TIMESPAN_IS_INVALID(*timespan))
      {
! #if FALSE
          elog(ERROR, "Timespan is not finite", NULL);
  #endif
          *result = 0;
--- 1843,1849 ----

      if (TIMESPAN_IS_INVALID(*timespan))
      {
! #if NOT_USED
          elog(ERROR, "Timespan is not finite", NULL);
  #endif
          *result = 0;
***************
*** 1893,1907 ****
                      break;

                  case DTK_DECADE:
!                     *result = (tm->tm_year / 10) + 1;
                      break;

                  case DTK_CENTURY:
!                     *result = (tm->tm_year / 100) + 1;
                      break;

                  case DTK_MILLENIUM:
!                     *result = (tm->tm_year / 1000) + 1;
                      break;

                  default:
--- 1893,1907 ----
                      break;

                  case DTK_DECADE:
!                     *result = (tm->tm_year / 10);
                      break;

                  case DTK_CENTURY:
!                     *result = (tm->tm_year / 100);
                      break;

                  case DTK_MILLENIUM:
!                     *result = (tm->tm_year / 1000);
                      break;

                  default:
***************
*** 2454,2460 ****
              tm->tm_mday = tx->tm_mday;
              tm->tm_hour = tx->tm_hour;
              tm->tm_min = tx->tm_min;
! #if FALSE
  /* XXX HACK
   * Argh! My Linux box puts in a 1 second offset for dates less than 1970
   *    but only if the seconds field was non-zero. So, don't copy the seconds
--- 2454,2460 ----
              tm->tm_mday = tx->tm_mday;
              tm->tm_hour = tx->tm_hour;
              tm->tm_min = tx->tm_min;
! #if NOT_USED
  /* XXX HACK
   * Argh! My Linux box puts in a 1 second offset for dates less than 1970
   *    but only if the seconds field was non-zero. So, don't copy the seconds
***************
*** 2814,2819 ****
--- 2814,2820 ----
      int            flen,
                  val;
      int            mer = HR24;
+     int            haveTextMonth = FALSE;
      int            is2digits = FALSE;
      int            bc = FALSE;

***************
*** 2955,2968 ****
  #ifdef DATEDEBUG
                          printf("DecodeDateTime- month field %s value is %d\n", field[i], val);
  #endif
                          tm->tm_mon = val;
                          break;

-                         /*
-                          * daylight savings time modifier (solves "MET
-                          * DST" syntax)
-                          */
                      case DTZMOD:
                          tmask |= DTK_M(DTZ);
                          tm->tm_isdst = 1;
                          if (tzp == NULL)
--- 2956,2978 ----
  #ifdef DATEDEBUG
                          printf("DecodeDateTime- month field %s value is %d\n", field[i], val);
  #endif
+                         /* already have a (numeric) month? then see if we can substitute... */
+                         if ((fmask & DTK_M(MONTH)) && (! haveTextMonth)
+                           && (!(fmask & DTK_M(DAY)))
+                           && ((tm->tm_mon >= 1) && (tm->tm_mon <= 31)))
+                         {
+                             tm->tm_mday = tm->tm_mon;
+                             tmask = DTK_M(DAY);
+ #ifdef DATEDEBUG
+                             printf("DecodeNumber- misidentified month previously; assign as day %d\n", tm->tm_mday);
+ #endif
+                         }
+                         haveTextMonth = TRUE;
                          tm->tm_mon = val;
                          break;

                      case DTZMOD:
+                         /* daylight savings time modifier (solves "MET DST" syntax) */
                          tmask |= DTK_M(DTZ);
                          tm->tm_isdst = 1;
                          if (tzp == NULL)
***************
*** 3466,3482 ****
          *tmask = DTK_M(YEAR);

          /* already have a year? then see if we can substitute... */
!         if (fmask & DTK_M(YEAR))
          {
!             if ((!(fmask & DTK_M(DAY)))
!                 && ((tm->tm_year >= 1) && (tm->tm_year <= 31)))
!             {
  #ifdef DATEDEBUG
!                 printf("DecodeNumber- misidentified year previously; swap with day %d\n", tm->tm_mday);
  #endif
-                 tm->tm_mday = tm->tm_year;
-                 *tmask = DTK_M(DAY);
-             }
          }

          tm->tm_year = val;
--- 3476,3489 ----
          *tmask = DTK_M(YEAR);

          /* already have a year? then see if we can substitute... */
!         if ((fmask & DTK_M(YEAR)) && (!(fmask & DTK_M(DAY)))
!           && ((tm->tm_year >= 1) && (tm->tm_year <= 31)))
          {
!             tm->tm_mday = tm->tm_year;
!             *tmask = DTK_M(DAY);
  #ifdef DATEDEBUG
!             printf("DecodeNumber- misidentified year previously; assign as day %d\n", tm->tm_mday);
  #endif
          }

          tm->tm_year = val;