Re: [GENERAL] to_timestamp() and quarters

Hello.

I can't seem to get to_timestamp() or to_date() to work with quarters,
can anyone see what I'm doing wrong?

e.g.
select to_date('2010-1', 'YYYY-Q');
Gives "2010-01-01" (correct).

select to_date('2010-3', 'YYYY-Q');
Also gives "2010-01-01" (should be 2010-07-01);

Am I misunderstanding how this function works? EXTRACT(QUARTER FROM
date) works fine.

I'm running Postgresql 8.4.2 on a FreeBSD system.

Thanks,
Asher.

Asher Hoskins <asher(at)piceur(dot)co(dot)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?

regards, tom lane

In response to Tom Lane :
> Asher Hoskins <asher(at)piceur(dot)co(dot)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'.
>

Ouch, sound like MySQL / myisam: we accept things like foreign key, but
we don't implement that...

+1 for raise an error or something, but no 'accept & ignore'.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

Tom Lane wrote:
> Asher Hoskins <asher(at)piceur(dot)co(dot)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.

Scott

Scott Bailey wrote:
> Tom Lane wrote:
> > Asher Hoskins <asher(at)piceur(dot)co(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do

In response to Bruce Momjian :
> 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.

Well, that's great and better than an error, thx.

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

Is this per SQL-Spec? I would expect an error for a quarter not in
(1,2,3,4).

But stop, now i see:

test=*# select to_date('2010-02-29', 'YYYY-MM-DD');
to_date
------------
2010-03-01
(1 row)

So it is maybe a congruously behavior ;-)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

A. Kretschmer *EXTERN*
> > 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.
>
> Well, that's great and better than an error, thx.
>
> > test=> select to_date('2010-7', 'YYYY-Q');
> > to_date
> > ------------
> > 2011-07-04
> > (1 row)
>
> Is this per SQL-Spec? I would expect an error for a quarter not in
> (1,2,3,4).
>
> But stop, now i see:
>
> test=*# select to_date('2010-02-29', 'YYYY-MM-DD');
> to_date
> ------------
> 2010-03-01
> (1 row)
>
> So it is maybe a congruously behavior ;-)

Ugh. I thought that to_date was an Oracle compatibility function.

SQL> select to_date('2010-02-29', 'YYYY-MM-DD') from dual;
select to_date('2010-02-29', 'YYYY-MM-DD') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified

And for that matter:

SQL> select to_date('2010-7', 'YYYY-Q') from dual;
select to_date('2010-7', 'YYYY-Q') from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Oracle allows Q only when converting date to string.
So this can be seen as an extension.

But allowing 2010-02-29 is incompatible and smacks of MySQL...

Yours,
Laurenz Albe

Albe Laurenz wrote:
> > But stop, now i see:
> >
> > test=*# select to_date('2010-02-29', 'YYYY-MM-DD');
> > to_date
> > ------------
> > 2010-03-01
> > (1 row)
> >
> > So it is maybe a congruously behavior ;-)
>
> Ugh. I thought that to_date was an Oracle compatibility function.
>
> SQL> select to_date('2010-02-29', 'YYYY-MM-DD') from dual;
> select to_date('2010-02-29', 'YYYY-MM-DD') from dual
> *
> ERROR at line 1:
> ORA-01839: date not valid for month specified
>
> And for that matter:
>
> SQL> select to_date('2010-7', 'YYYY-Q') from dual;
> select to_date('2010-7', 'YYYY-Q') from dual
> *
> ERROR at line 1:
> ORA-01820: format code cannot appear in date input format
>
> Oracle allows Q only when converting date to string.
> So this can be seen as an extension.
>
> But allowing 2010-02-29 is incompatible and smacks of MySQL...

Yea, we had a similar issue with to_timestamp():

test=> SELECT to_timestamp('20096040','YYYYMMDD');
to_timestamp
------------------------
2014-01-17 00:00:00-05
(1 row)

If we are going to tighten these up, we should do them all. Right now
we allow it and for consistency should allow the Q=7 value too.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do

On 3 March 2010 14:34, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Scott Bailey wrote:
>> Tom Lane wrote:
>> > Asher Hoskins <asher(at)piceur(dot)co(dot)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)

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.

Cheers,
BJ

[1] http://archives.postgresql.org/message-id/37ed240d0707170747p4f5c26ffx63fff2b5750c62e5@mail.gmail.com

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do

Brendan Jurd <direvus(at)gmail(dot)com> 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 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.

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.

regards, tom lane

Tom Lane wrote:
> Brendan Jurd <direvus(at)gmail(dot)com> 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 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.
>
> 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', 'YYYY-Q');
to_date
------------
2010-07-01
(1 row)

test=> SELECT to_date('2010-04-3', 'YYYY-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', 'YYYY-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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do

On 4 March 2010 04:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Brendan Jurd <direvus(at)gmail(dot)com> 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 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.
>
> 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.

Not entirely true. It's possible, it's just not at all obvious:

=# select to_date('Wed 3rd March, Q1 2010', 'Dy FMDDTH Month, "QQ" YYYY');
to_date
------------
2010-03-03
(1 row)

Anything in a format string which is quoted is ignored. Or to put it
another way, putting stuff in quotes is telling to_date() that the
characters in those positions are not important to you and should not
be used to help construct the date result. It doesn't actually check
that the characters in the source string match what you have put
inside the quotes, it just skips over the quoted number of characters.

I doubt anyone unfamiliar with the source code of the function would
ever devise the above solution, and it's an ugly hack reliant on a
quirk anyway. So a user in-the-field would probably just resort to
running a regexp_replace() over the text in order to strip out the
quarter before passing it to to_date().

> 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.

Sounds good to me.

Cheers,
BJ

Bruce Momjian <bruce(at)momjian(dot)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.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do

A. Kretschmer wrote:
> In response to Tom Lane :
>> Asher Hoskins <asher(at)piceur(dot)co(dot)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'.
>>
>
> Ouch, sound like MySQL / myisam: we accept things like foreign key, but
> we don't implement that...
>
> +1 for raise an error or something, but no 'accept & ignore'.

Thanks for the replies, nice to know I wasn't doing something silly! +1
for raising an error/warning from me too, it would have saved me some
fruitless googling.

Thanks,
Asher.