Re: [GENERAL] to_timestamp() and quarters

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