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

The following bug has been logged online:

Bug reference: 2403
Logged by: Harry E. Clarke
Email address: Harry(dot)Clarke(at)metrosky(dot)co(dot)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.

Harry E. Clarke wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2403
> Logged by: Harry E. Clarke
> Email address: Harry(dot)Clarke(at)metrosky(dot)co(dot)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. +

On Sat, Apr 22, 2006 at 20:34:03 +0000,
"Harry E. Clarke" <Harry(dot)Clarke(at)metrosky(dot)co(dot)uk> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2403
> Logged by: Harry E. Clarke
> Email address: Harry(dot)Clarke(at)metrosky(dot)co(dot)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.

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.

regards, tom lane

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Tom Lane wrote:

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

I think this is a perfect project for Summer of Code.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> Actually implementing this is left as a task for someone who feels like
>> hacking on the datetime code ... I don't particularly ...

> I think this is a perfect project for Summer of Code.

That's a thought. Another task in the same area is to fix things so
that timezone abbreviations can be user-configured. Neither one ought
to be a full summer, but maybe both together ...

regards, tom lane