Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Converting Seconds to hh:mm:ss format without using Interval() function

Hello,

I have a data set where some time values are presented in seconds. Data on the chart should be displayed in hh:mm:ss format.

I've used the Interval() function in the script:

     Interval(time_field/86400, 'hh:mm:ss') as TIME_VALUE

and everything was looking good.

But,

I need to avoid any data transformation in my script and simply rely on data from the database.

I was hoping that using the same approach on the data side (code from the View Script):

     CAST (TIME_SEC / 84600 AS NUMBER (20, 18)) AS TIME_FULL

will work the same way, but I've noticed some rounding (?) issues.

With small values in seconds, everything looks good:

time_1.jpg

With values growing, the error increases:

time_2.jpg

And the difference reaches significant values when time in seconds represent large interval (more than 2 minutes difference in ~1,5 hours range....):

time_3.jpg

I could not find any reasonable explanation for that.

I would appreciate any suggestions.

Regards,

Vladimir

6 Replies
MVP
MVP

Re: Converting Seconds to hh:mm:ss format without using Interval() function

danieloberbilli
Valued Contributor II

Re: Converting Seconds to hh:mm:ss format without using Interval() function

http://qlikviewcookbook.com/2011/10/correct-time-arithmetic/

I agree with tresesco - this is the right source. Especially have a look at Robs comment: avoid using division to convert time.

See also the link above

MVP
MVP

Re: Converting Seconds to hh:mm:ss format without using Interval() function

Hi

You want to avoid transforming the data in QV - that's fine. Interval() does not transform the data, it simply formats to hh:mm:ss (or other format that you specify) - the underlying data value is unchanged.

You can prove this to yourself by entering something like this in a text box:

     =Num(Interval(3233))

Num gets the underlying value...

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Converting Seconds to hh:mm:ss format without using Interval() function

Jonathan,

Regardless the transformation definition:

Why TIME_1 and TIME_2 are significantly different?

Interval(TIME_SECONDS/86400, 'hh:mm:ss') as TIME_1,  
Interval(TIME_DIV_BY_86400, 'hh:mm:ss') as TIME_2,

Somehow QV know how to round the value correctly??

Partner
Partner

Re: Converting Seconds to hh:mm:ss format without using Interval() function

It's good article and nice discussion , but it's still not clear for me why QV's Interval() function works better in this case...

Interval(TIME_SECONDS/86400, 'hh:mm:ss') as TIME_1,   // result is correct 

Interval(TIME_DIV_BY_86400, 'hh:mm:ss') as TIME_2,       // result is wrong

Somehow QV know how to round the value correctly??

Partner
Partner

Re: Converting Seconds to hh:mm:ss format without using Interval() function

Excel works perfectly too...

SECONDSSECONDS/86400EXCEL FORMATTED
58650.0678819444444444001:37:45