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

Brendan Jurd wrote:
> > 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)
>
> I don't think this is the way to go.  Why should the "date" for
> quarter 1, 2010 be the first date of that quarter?  Why not the last
> date?  Why not some date in between?
>
> A quarter on its own doesn't assist us in producing a *date* result,
> which is after all the purpose of the to_date() function.
>
> I first proposed ignoring the Q field back in 2007 [1].  My motivation
> for not throwing an error was that I think the main use-case for
> to_date() would be importing data from another system where dates are
> in a predictable but non-standard format.
>
> If such a date included the quarter, the user might expect to be able
> to include the quarter in his format string.
>
> 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.
>
> Having said all of that, it's been pointed out to me in the past that
> Oracle compatibility is the main goal of these functions, so if we're
> going to change the behaviour of Q in to_date(), I think it should be
> in order to move closer to Oracle's treatment.  I certainly don't
> think we should get back into the business of delivering an exact
> answer to an inexact question.  So a +1 for throwing the error per Tom
> Lane and Scott Bailey.

OK, patch attached that throws an error:

    test=> SELECT to_date('2010-7', 'YYYY-Q');
    ERROR:  "Q" format is not supported in to_date

--
  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:06:59 -0000
***************
*** 2671,2686 ****
                  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:
                  from_char_parse_int(&out->cc, &s, n);
--- 2671,2680 ----
                  s += SKIP_THth(n->suffix);
                  break;
              case DCH_Q:
!                 /* It is unclear which date in the quarter to return. */
!                 ereport(ERROR,
!                         (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
!                          errmsg("\"Q\" format is not supported in to_date")));
                  break;
              case DCH_CC:
                  from_char_parse_int(&out->cc, &s, n);