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

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073