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 <br...@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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
