duration calculation

General
Enthusiast

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

Hi,

 

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

 

Cheers,

Dave

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

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