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

Re: BUG #1518: Conversions to (undocumented) SQL year-month

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Roy Badami <roy(at)gnomon(dot)org(dot)uk>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1518: Conversions to (undocumented) SQL year-month
Date: 2005-03-23 23:06:40
Message-ID: 200503232306.j2NN6eK28908@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Roy Badami wrote:
> 
>     Tom> In order to support the spec *exactly*, we would have to.
>     Tom> For instance we cannot presently tell the difference between
>     Tom> '13 months' and '1 year 1 month' ... they both end up stored
>     Tom> as '13 months'.  I can't say that I find this very important,
>     Tom> but it does put limits on how exactly we can emulate the
>     Tom> spec.
> 
> Which is where my comment about EXTRACT comes in.  They can both be
> stored as 13 months, but EXTRACT (MONTH FROM ...) should return 1
> month or 13 months as appropriate.  Surely this isn't a problem, you
> know the type of the interval?
> 

What happens if you store '13 months' into an interval column that is
YEAR TO MONTH?  Does extract MONTH return 1 or 13?

Right now we return one:

	test=> select extract(month from interval '13 months' year to month);
	 date_part
	-----------
	         1
	(1 row)

but this seems strange:
	
	test=> select extract(month from interval '13 months' month);
	 date_part
	-----------
	         1
	(1 row)

It is because it is really '1 year 1 month':
	
	test=> select interval '13 months' as month;
	    month
	--------------
	 1 year 1 mon
	(1 row)

What I would really like to avoid is having a different internal
representation for ANSI and PG interval values.

The lack of complaints all these years perhaps means people either don't
care or accept the PG behavior.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-03-23 23:07:56
Subject: Re: BUG #1518: Conversions to (undocumented) SQL year-month and
Previous:From: Tom LaneDate: 2005-03-23 23:04:52
Subject: Re: BUG #1517: SQL interval syntax is accepted by the parser,

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