Re: [GENERAL] to_timestamp() and quarters

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