Re: BUG #2403: Date arithemtic using INTERVAL in UPDATE command

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > That is definately a bug:
> >     test=> SELECT INTERVAL '100' YEAR;
>
> We don't currently support that style of specifying interval constants,
> and you shouldn't hold your breath expecting it to happen --- it will
> require a whole bunch of abuse of the currently data-type-independent
> processing of literal constants.  I don't think anyone's even thought
> about it since Tom Lockhart stopped working on that part of the code.
> The fact that the syntax is accepted at all is just because he had
> done some preliminary work on the grammar, but there's no infrastructure
> behind the grammar for handling it.
>
> In short, you need to calibrate your expectations as "feature addition
> someday", not "bug fix".
>
> > As a work-around until we fix it, please use:
> >     test=> SELECT INTERVAL '100 year';
>
> This is the syntax we support.

I did some more research on this item, and updated the TODO item:

        o Support ISO INTERVAL syntax if units cannot be determined from
          the string, and are supplied after the string

          The SQL standard states that the units after the string
          specify the units of the string, e.g. INTERVAL '2' MINUTE
          should return '00:02:00'. The current behavior has the units
          restrict the interval value to the specified unit or unit
          range, INTERVAL '70' SECOND returns '00:00:10'.

          For syntax that isn't uniquely ISO or PG syntax, like '1' or
          '1:30', treat as ISO if there is a range specification clause,
          and as PG if there no clause is present, e.g. interpret '1:30'
          MINUTE TO SECOND as '1 minute 30 seconds', and interpret
          '1:30' as '1 hour, 30 minutes'.

          This makes common cases like SELECT INTERVAL '1' MONTH
          SQL-standard results. The SQL standard supports a limited
          number of unit combinations and doesn't support unit names in
          the string. The PostgreSQL syntax is more flexible in the
          range of units supported, e.g. PostgreSQL supports '1 year 1
          hour', while the SQL standard does not.

I hope this helps.

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +