Обсуждение: to_timestamp() and quarters

Поиск
Список
Период
Сортировка

to_timestamp() and quarters

От
Asher Hoskins
Дата:
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.

Re: to_timestamp() and quarters

От
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'.

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

Re: to_timestamp() and quarters

От
"A. Kretschmer"
Дата:
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

Re: to_timestamp() and quarters

От
Scott Bailey
Дата:
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

Re: to_timestamp() and quarters

От
Asher Hoskins
Дата:
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.