Re: [GENERAL] to_timestamp() and quartersf - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Here is an updated patch that honors 'Q' only if the month has not been
> > previously supplied:
>
> That's just weird.  It's not even self-consistent much less
> unsurprising --- having the behavior be dependent on field order is
> really horrid.
>
> I think what people would actually want for this type of situation is
> a way to specify "there is an integer here but I want to ignore it".
> Q as it's presently constituted accomplishes that, though it is not
> documented as doing so.  Brendan's comment about quoted text is
> interesting, but it doesn't really solve the problem because of the
> possibility of the integer field being variable width.

I have updated the comments that "Q" is ignored by to_date and
to_timestamp, and added a C comment.

I also documented the double-quote input-skip behavior of to_timestamp,
to_number, and to_date.

Applied patch attached.

--
  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: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.506
diff -c -c -r1.506 func.sgml
*** doc/src/sgml/func.sgml    23 Feb 2010 16:14:25 -0000    1.506
--- doc/src/sgml/func.sgml    3 Mar 2010 22:27:36 -0000
***************
*** 5089,5095 ****
         </row>
         <row>
          <entry><literal>Q</literal></entry>
!         <entry>quarter</entry>
         </row>
         <row>
          <entry><literal>RM</literal></entry>
--- 5089,5095 ----
         </row>
         <row>
          <entry><literal>Q</literal></entry>
!         <entry>quarter (ignored by <function>to_date</> and <function>to_timestamp</>)</entry>
         </row>
         <row>
          <entry><literal>RM</literal></entry>
***************
*** 5209,5215 ****
         even if it contains pattern key words.  For example, in
         <literal>'"Hello Year "YYYY'</literal>, the <literal>YYYY</literal>
         will be replaced by the year data, but the single <literal>Y</literal> in <literal>Year</literal>
!        will not be.
        </para>
       </listitem>

--- 5209,5218 ----
         even if it contains pattern key words.  For example, in
         <literal>'"Hello Year "YYYY'</literal>, the <literal>YYYY</literal>
         will be replaced by the year data, but the single <literal>Y</literal> in <literal>Year</literal>
!        will not be.  In <function>to_date</>, <function>to_number</>,
!        and <function>to_timestamp</>, double-quoted strings skip the number of
!        input characters contained in the string, e.g. <literal>"XX"</>
!        skips two input characters.
        </para>
       </listitem>

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 22:27:38 -0000
***************
*** 2671,2680 ****
                  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'.
--- 2671,2682 ----
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_Q:
                  /*
!                  * We ignore 'Q' when converting to date because it is
!                  * unclear which date in the quarter to use, and some
!                  * people specify both quarter and month, so if it was
!                  * honored it might conflict with the supplied month.
!                  * That is also why we don't throw an error.
                   *
                   * We still parse the source string for an integer, but it
                   * isn't stored anywhere in 'out'.