Re: [GENERAL] to_timestamp() and quarters - Mailing list pgsql-hackers
From | Brendan Jurd |
---|---|
Subject | Re: [GENERAL] to_timestamp() and quarters |
Date | |
Msg-id | 37ed240d1003030851x5139b41escf592415111b50e6@mail.gmail.com Whole thread Raw |
In response to | Re: [GENERAL] to_timestamp() and quarters (Bruce Momjian <bruce@momjian.us>) |
Responses |
Re: [GENERAL] to_timestamp() and quarters
Re: [GENERAL] to_timestamp() and quarters |
List | pgsql-hackers |
On 3 March 2010 14:34, Bruce Momjian <bruce@momjian.us> wrote: > Scott Bailey wrote: >> 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. > > 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
pgsql-hackers by date: