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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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