Re: [HACKERS] [GENERAL] to_timestamp() and quartersf
Tom Lane wrote: > Bruce Momjian 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 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 - 1.506 --- doc/src/sgml/func.sgml 3 Mar 2010 22:27:36 - *** *** 5089,5095 Q ! quarter RM --- 5089,5095 Q ! quarter (ignored by to_date and to_timestamp) RM *** *** 5209,5215 even if it contains pattern key words. For example, in '"Hello Year "', the will be replaced by the year data, but the single Y in Year !will not be. --- 5209,5218 even if it contains pattern key words. For example, in '"Hello Year "', the will be replaced by the year data, but the single Y in Year !will not be. In to_date, to_number, !and to_timestamp, double-quoted strings skip the number of !input characters contained in the string, e.g. "XX" !skips two input characters. 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 - 1.168 --- src/backend/utils/adt/formatting.c 3 Mar 2010 22:27:38 - *** *** 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'. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quartersf
Bruce Momjian 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. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quartersf
Tom Lane wrote:
> Brendan Jurd 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 ' 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', '-Q');
to_date
2010-07-01
(1 row)
test=> SELECT to_date('2010-04-3', '-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', '-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 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 - 1.168
--- src/backend/utils/adt/formatting.c 3 Mar 2010 17:18:43 -
***
*** 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:
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
