Interval Overflow Error

UDA
Fan

Interval Overflow Error

Hi,

I am having a column where the time is sorted as ddd hh:min:ss. The col has many 3 digit days & when database sums them all...the total is more than "4 digit days".

If I try for interval: day(5) to second(0)....its not working.

Is there a way to accomodate sum of days more than 4 digits?

953 21:34:15
846 19:54:20
748 03:24:65
702 11:34:94
675 23:42:58
644 20:13:40
562 23:08:55
534 16:12:39
468 12:53:58
394 15:25:14
343 08:12:28
298 07:30:98
250 05:39:74

Let me know, how it can be resolved. Thanks
1 REPLY
Enthusiast

Re: Interval Overflow Error

The code below converts each interval to days (with decimal part-days), adds them up and then converts it back to a character interval looking string with up to 5 days.

You can only have a maximum of Days(4) to Second interval. (This can easily be upded to larger total of days if things start to take even longer in your busiiness!)

-------------------------------------------

Set up Test Data:

Create Volatile Table T1
(Pk Smallint Not Null
, IntvlCol Interval Day(3) TO SECOND(0)
)
Unique Primary Index (PK)
On Commit preserve Rows
;

Insert Into T1 Values (1,'953 21:34:15');
Insert Into T1 Values (2,'846 19:54:20');
Insert Into T1 Values (3,'748 03:24:65');
Insert Into T1 Values (4,'702 11:34:94');
Insert Into T1 Values (5,'675 23:42:58');
Insert Into T1 Values (6,'644 20:13:40');
Insert Into T1 Values (7,'562 23:08:55');
Insert Into T1 Values (8,'534 16:12:39');
Insert Into T1 Values (9,'468 12:53:58');
Insert Into T1 Values (10,'394 15:25:14');
Insert Into T1 Values (11,'343 08:12:28');
Insert Into T1 Values (12,'298 07:30:98');
Insert Into T1 Values (13,'250 05:39:74');

----------------------------------------------
Add them up:

Select
Sum(
Extract(Day From IntvlCol) +
((Extract(Hour From IntvlCol) * 60 * 60)
+ (Extract(Minute From IntvlCol) *60)
+ Extract(Second From IntvlCol)) / (24.0000 * 60 * 60) ) (Named AllInts)
, Trim(Cast((Allints (Integer)) As Char(6)))||' '
||Cast((Allints Mod 1.0000 *24 (ByteInt)) AS Char(2))||':'
||Cast(((AllInts Mod 1.0000 * 24 * 60 Mod 60) (ByteInt)) As Char(2))||':'
||Cast(((AllInts Mod 1.0000 * 24 * 60 * 60 ) Mod 60 (ByteInt)) As Char(2)) As BigIntvl

From T1
Order By 1
;

HTH