duration calculation


Re: duration calculation

Thanks Dave!


Final question here.. Just alsow wanted to understand how the difference in timestamp can be converted to varchar(255)


sel(start_ts - end_ts) hour(4) to second(0) as diff from table


I wanted to insert into a table of a 'diff' column which has varchar field. Any lights on this?

Senior Apprentice

Re: duration calculation



Use CAST - standard SQL syntax for converting from one data type to another.




Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: duration calculation

Hi Dave,


Ya I was able to do bove with tochar function. That's fine.


Got stuck with one more.


In the below query below are the datatypes,

dur - time(0)

start_ts - timestamp(6)

end_ts - timestamp(6)


Getting interval field overflow error when I execute select.

insert into table a(dur)sel
CAST(SUBSTRING(('0000'||TRIM(CAST( (((start_ts - end_ts) HOUR(4) TO SECOND(0))) AS CHAR(11)))) FROM CHARS('0000'||TRIM(CAST( (((start_ts - end_ts) HOUR(4) TO SECOND(0))) AS CHAR(11))))-7) AS TIME(0)) AS dur
from table b;


Junior Contributor

Re: duration calculation

As Dave already said, the source columns are defined with a higher precision than SECOND(0). Thus you need to change the Substring accordingly to truncate the subseconds.


But this can be simplified to:

TIME '00:00:00' + (endtime - starttime HOUR(4) TO SECOND(6))