Thread: 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@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?

            regards, tom lane

In response to Tom Lane :
> 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'.
>

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

Scott

A. Kretschmer wrote:
> In response to Tom Lane :
>> 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'.
>>
>
> 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.