The following bug has been logged online:

Bug reference: 2403
Logged by: Harry E. Clarke
Email address: Harry.Clarke@metrosky.co.uk
PostgreSQL version: 8.1.3
Operating system: Suse Linux 10.0
Description: Date arithemtic using INTERVAL in UPDATE command does
not work
Details:

Entering the command

UPDATE table SET col_date = col_date - INTERVAL '100' YEAR where condition;

does not perform any date aritimetic, and thus the date value in col_date
remains unchanged. col_date contains a value such as '2039-12-07'. If the
command

UPDATE table SET col_date = DATE '1939-12-07' where condition;

is entered, the command executes as expected.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Harry E. Clarke wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2403
> Logged by: Harry E. Clarke
> Email address: Harry.Clarke@metrosky.co.uk
> PostgreSQL version: 8.1.3
> Operating system: Suse Linux 10.0
> Description: Date arithemtic using INTERVAL in UPDATE command does
> not work
> Details:
>
> Entering the command
>
> UPDATE table SET col_date = col_date - INTERVAL '100' YEAR where condition;
>
> does not perform any date aritimetic, and thus the date value in col_date
> remains unchanged. col_date contains a value such as '2039-12-07'. If the
> command
>
> UPDATE table SET col_date = DATE '1939-12-07' where condition;
>
> is entered, the command executes as expected.

That is definately a bug:

test=> SELECT INTERVAL '100' YEAR;
interval
----------
00:00:00
(1 row)

As a work-around until we fix it, please use:

test=> SELECT INTERVAL '100 year';
interval
-----------
100 years
(1 row)

with the 'YEAR' in the quotes.

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sat, Apr 22, 2006 at 20:34:03 +0000,
"Harry E. Clarke" <Harry.Clarke@metrosky.co.uk> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2403
> Logged by: Harry E. Clarke
> Email address: Harry.Clarke@metrosky.co.uk
> PostgreSQL version: 8.1.3
> Operating system: Suse Linux 10.0
> Description: Date arithemtic using INTERVAL in UPDATE command does
> not work
> Details:
>
> Entering the command
>
> UPDATE table SET col_date = col_date - INTERVAL '100' YEAR where condition;
>
> does not perform any date aritimetic, and thus the date value in col_date
> remains unchanged. col_date contains a value such as '2039-12-07'. If the
> command
>
> UPDATE table SET col_date = DATE '1939-12-07' where condition;
>
> is entered, the command executes as expected.

You probably just made a mistake with the condition. But since you haven't
shown us what it was, we can't give you any specific advice about it.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> 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 did some more research on this item, and updated the TODO item:

BTW, I looked at the problem a little bit and concluded that it wouldn't
be so invasive to fix as all that. The weak spot at the moment is that
parse_coerce() passes typmod -1 instead of the specified typmod to the
datatype's input routine when converting an unknown-type literal. It
has to do that to get the right behavior for varchar(N) and char(N)
.... but we could imagine hacking it to behave differently for interval.
At the most grotty,

if (targetTypeId == INTERVALOID)
pass targetTypeMod;
else
pass -1;

but maybe something cleaner could be devised. That would take care of
getting the info to interval_in(), and then the question is what
interval_in() should do with it. Your notes in the TODO entry look
like they summarize previous discussion accurately.

It's worth pointing out that this would also affect data input, eg
COPY into an interval column would interpret '100' differently depending
on how the column had been declared. I think this is OK but it'd need
some consideration.

Actually implementing this is left as a task for someone who feels like
hacking on the datetime code ... I don't particularly ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq