Re: [GENERAL] date comparisons - Mailing list pgsql-patches

OK, I researched this and have developed the attached patch, which I
have applied to CVS HEAD.

The problem with our code is that when using to_timestamp() or to_date()
without "TM", we assume a fixed maximum length, even if the input
string is variable length, like month or day names.  Oracle assumes "TM"
(trim) for such input fields, and this patch does the same.

I think it is too risky to backpatch to 8.2.X.

If anyone sees anymore cases of this in the code, please let me know.

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

Tom Lane wrote:
> "Belinda M. Giardine" <giardine@bio.cse.psu.edu> writes:
> > Should it be this way?
>
> Well, to_timestamp() is apparently designed not to complain when the
> input doesn't match the format, which is not my idea of good behavior
> ... but your example is in fact wrong.  'Month' means a 9-character
> field, so you are short a couple of spaces.
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
>       to_timestamp
> ------------------------
>  0006-01-01 00:00:00-05
> (1 row)
>
> regression=# select to_timestamp('January   2006', 'Month YYYY');
>       to_timestamp
> ------------------------
>  2006-01-01 00:00:00-05
> (1 row)
>
> You probably want
>
> regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
>       to_timestamp
> ------------------------
>  2006-01-01 00:00:00-05
> (1 row)
>
> Or, as suggested upthread, forget to_timestamp and just use the native
> timestamp or date input conversion, which on the whole is a lot more
> robust (it *will* throw an error if it can't make sense of the input,
> unlike to_timestamp).
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.118
diff -c -c -r1.118 formatting.c
*** src/backend/utils/adt/formatting.c    12 Jan 2007 23:34:54 -0000    1.118
--- src/backend/utils/adt/formatting.c    8 Feb 2007 02:17:16 -0000
***************
*** 2093,2102 ****
          {
              tmfc->mm = seq_search(inout, months_full, ONE_UPPER, FULL_SIZ, &len) + 1;
              CHECK_SEQ_SEARCH(len, "MONTH/Month/month");
!             if (S_FM(suf))
!                 return len;
!             else
!                 return 9;
          }
          else if (arg == DCH_MON || arg == DCH_Mon || arg == DCH_mon)
          {
--- 2093,2099 ----
          {
              tmfc->mm = seq_search(inout, months_full, ONE_UPPER, FULL_SIZ, &len) + 1;
              CHECK_SEQ_SEARCH(len, "MONTH/Month/month");
!             return len;
          }
          else if (arg == DCH_MON || arg == DCH_Mon || arg == DCH_mon)
          {
***************
*** 2108,2117 ****
          {
              tmfc->d = seq_search(inout, days, ONE_UPPER, FULL_SIZ, &len);
              CHECK_SEQ_SEARCH(len, "DAY/Day/day");
!             if (S_FM(suf))
!                 return len;
!             else
!                 return 9;
          }
          else if (arg == DCH_DY || arg == DCH_Dy || arg == DCH_dy)
          {
--- 2105,2111 ----
          {
              tmfc->d = seq_search(inout, days, ONE_UPPER, FULL_SIZ, &len);
              CHECK_SEQ_SEARCH(len, "DAY/Day/day");
!             return len;
          }
          else if (arg == DCH_DY || arg == DCH_Dy || arg == DCH_dy)
          {
***************
*** 2636,2645 ****
              {
                  tmfc->mm = 12 - seq_search(inout, rm_months_upper, ALL_UPPER, FULL_SIZ, &len);
                  CHECK_SEQ_SEARCH(len, "RM");
!                 if (S_FM(suf))
!                     return len;
!                 else
!                     return 4;
              }
              break;
          case DCH_rm:
--- 2630,2636 ----
              {
                  tmfc->mm = 12 - seq_search(inout, rm_months_upper, ALL_UPPER, FULL_SIZ, &len);
                  CHECK_SEQ_SEARCH(len, "RM");
!                 return len;
              }
              break;
          case DCH_rm:
***************
*** 2655,2664 ****
              {
                  tmfc->mm = 12 - seq_search(inout, rm_months_lower, ALL_LOWER, FULL_SIZ, &len);
                  CHECK_SEQ_SEARCH(len, "rm");
!                 if (S_FM(suf))
!                     return len;
!                 else
!                     return 4;
              }
              break;
          case DCH_W:
--- 2646,2652 ----
              {
                  tmfc->mm = 12 - seq_search(inout, rm_months_lower, ALL_LOWER, FULL_SIZ, &len);
                  CHECK_SEQ_SEARCH(len, "rm");
!                 return len;
              }
              break;
          case DCH_W: