Re: [GENERAL] to_timestamp() and quartersf

Tom Lane wrote:
> Brendan Jurd <direvus@gmail.com> writes:
> > For example, you're trying to import a date that is written as "Wed
> > 3rd March, Q1 2010".  You might give to_date a format string like 'Dy
> > FMDDTH Month, "Q"Q YYYY' and expect to get the correct answer.  If we
> > start throwing an error on the Q field, then users would have to
> > resort to some strange circumlocution to get around it.
>
> Hmm.  That's an interesting test case: if Q throws an error, there
> doesn't seem to be any way to do it at all, because there is no format
> spec for ignoring non-constant text.  Conversely, Bruce's proposed
> patch would actually break it, because the Q code would overwrite the
> (correct) month information with the first-month-of-the-quarter.
>
> So at the moment my vote is "leave it alone".  If we want to throw
> error for Q then we should provide a substitute method of ignoring
> a field.  But we could just document Q as ignoring an integer for
> input.

Here is an updated patch that honors 'Q' only if the month has not been
previously supplied:

    test=> SELECT to_date('2010-3', 'YYYY-Q');
      to_date
    ------------
     2010-07-01
    (1 row)

    test=> SELECT to_date('2010-04-3', 'YYYY-MM-Q');
      to_date
    ------------
     2010-04-01
    (1 row)

but it fails if a later month is specified:

    test=> select to_date('2010-3-05', 'YYYY-Q-MM');
    ERROR:  conflicting values for "MM" field in formatting string
    DETAIL:  This value contradicts a previous setting for the same field type.

even if the month is in that quarter but not the first month of the
quarter.

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

  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.168
diff -c -c -r1.168 formatting.c
*** src/backend/utils/adt/formatting.c    26 Feb 2010 02:01:08 -0000    1.168
--- src/backend/utils/adt/formatting.c    3 Mar 2010 17:18:43 -0000
***************
*** 2671,2685 ****
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_Q:
!
!                 /*
!                  * We ignore Q when converting to date because it is not
!                  * normative.
!                  *
!                  * We still parse the source string for an integer, but it
!                  * isn't stored anywhere in 'out'.
!                  */
!                 from_char_parse_int((int *) NULL, &s, n);
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_CC:
--- 2671,2684 ----
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_Q:
!                 /* Honor "Q" only if a month has not previously be set */
!                 if (out->mm == 0)
!                 {
!                     from_char_parse_int(&out->mm, &s, n);
!                     out->mm = (out->mm - 1) * 3 + 1;
!                 }
!                 else    /* ignore */
!                     from_char_parse_int((int *) NULL, &s, n);
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_CC: