Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] to_timestamp() and quarters

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Scott Bailey <artacus(at)comcast(dot)net>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [GENERAL] to_timestamp() and quarters
Date: 2010-03-03 03:34:30
Message-ID: 201003030334.o233YVj21325@momjian.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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)
	
	test=> select to_date('2010-3', 'YYYY-Q');
	  to_date
	------------
	 2010-07-01
	(1 row)
	
	test=> select to_date('2010-7', 'YYYY-Q');
	  to_date
	------------
	 2011-07-04
	(1 row)

-- 
  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

Attachment: /pgpatches/quarter
Description: text/x-diff (1.0 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Jaime CasanovaDate: 2010-03-03 04:02:54
Subject: Re: pgsql: Instead of trying (and failing) to allow <<label>> at the end of
Previous:From: Andrew DunstanDate: 2010-03-03 03:23:56
Subject: Re: USE_LIBXSLT in MSVC builds

pgsql-general by date

Next:From: Harvey, Allan ACDate: 2010-03-03 03:59:12
Subject: Re: SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?
Previous:From: Ozz NixonDate: 2010-03-03 01:59:39
Subject: Re: [SOLVED] SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group