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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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. +