Re: [GENERAL] to_timestamp() and quarters

Scott Bailey wrote:
> Tom Lane wrote:
> > Asher Hoskins <asher@piceur.co.uk> writes:
> >> I can't seem to get to_timestamp() or to_date() to work with quarters,
> >
> > The source code says
> >
> >                  * 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'.
> >
> > That might be a reasonable position, but it seems like it'd be better to
> > throw an error than silently do nothing.  Anybody know what Oracle does
> > with this?
>
> +1 for throwing error.
> Oracle 10g throws ORA-01820: format code cannot appear in date input format.

Well, I can easily make it do what you expect, and I don't see many
error returns in that area of the code, so I just wrote a patch that
does what you would expect rather than throw an error.

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

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

    test=> select to_date('2010-7', 'YYYY-Q');
      to_date
    ------------
     2011-07-04
    (1 row)

--
  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 03:29:05 -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,2678 ----
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_Q:
!                 from_char_parse_int(&out->mm, &s, n);
!                 out->mm = (out->mm - 1) * 3 + 1;
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_CC: