Update Interval column on Interval substraction

Database
Enthusiast

Update Interval column on Interval substraction

Hi, i have these 2 columns defined:

WAIT_INTVL INTERVAL HOUR(3) TO SECOND(2)

HOLD_INTVL INTERVAL HOUR(3) TO SECOND(2)

and both are null values

a) select COALESCE(WAIT_INTVL, INTERVAL '0' SECOND) from TableT1

returns     0:00:00.00

b) i can substract (a) with itself

select COALESCE(WAIT_INTVL, INTERVAL '0' SECOND) - COALESCE(WAIT_INTVL, INTERVAL '0' SECOND)

from TableT1

returns     0:00:00.00

c) Now, when i try to insert the above result into the INTERVAL column, i get error:

update TableT1

set HALT_INTVL =  COALESCE(WAIT_INTVL, INTERVAL '0' SECOND) - COALESCE(WAIT_INTVL, INTERVAL '0' SECOND)

It throws the error:

5405: Interval field overflow

why?

Note: My actual code does difference on 2 different columns and at runtime if they are both null (and the code colesces them to Zero interval) then the

sql fails at this update statement

Thanks,

-srinivas yelamanchili

2 REPLIES
Senior Supporter

Re: Update Interval column on Interval substraction

check the type of INTERVAL '0' SECOND - it is not an interval hour(3) to second(2).

Try cast('0:00:00' as interval Hour(3) to Second(2)) instead.

Enthusiast

Re: Update Interval column on Interval substraction

It worked after i added CAST to addition or substration of interval columns.

Even if the columns are not nullable and i don't use COALESCE, the sum of two intervals fail to load into an interval column, even if all the intervals are of same precision and even if the sum is not an overflow value.

I just had to cast the result of evaluation before assigning to an update column

Thanks,

-sri