Interval() seconds to Num Format

[#1]

I need to convert the difference between two time stamps (in seconds) to the Num() format so that I can perform calculations using the result.

I am using the interval function to calculate the number of seconds between two time stamps:

Interval('9:08:15 AM' - '9:07:42 AM', 'ss')

The result for the above example is 33 seconds, which is correct.

I want to be able to add a number from another field to those seconds. For example, add 5 to the result above. If I try to perform this addition, the interval value reverts to a time decimal. For example:

5 + Interval('9:08:15 AM' - '9:07:42 AM', 'ss') = 15.000381944444

The result I am looking for is 38 seconds. Using any combination of Num() and Text() has not helped.

Any ideas?

[swuehl]

Try Interval('9:08:15 AM' - '9:07:42 AM' + maketime(0,0,5), 'ss')

[#3]

I should have added that I need to multiple the resulting seconds by a dollar amount. This helps to get the total number of seconds, but I still need the result to be in a number format to perform additional calculations.

It looks like the this might actually get the correct answer:

Num(Interval('9:08:15 AM' - '9:07:42 AM', 'ss')  *24*60*60)

[swuehl · Accepted solution]

Right, QV time and date values are numbers and stored as fractions of days.

Interval('9:08:15 AM' - '9:07:42 AM', 'ss') is just formatting the underlying number ( a small fraction)  as text showing 33.

So you need to multiply the underlying number with 24*60*60 to get a number 'in seconds', or you could try

Num#(Interval('9:08:15 AM' - '9:07:42 AM', 'ss')

QlikView Date fields

[maxgro]

I think

=interval(Timestamp#('09:08:15 AM', 'hh:mm:ss tt') - Timestamp#('9:07:42 AM', 'hh:mm:ss tt') + interval#(5, 'ss'), 'ss')

[Dalton_Ruer]

Uggghhhh so many times in the past I struggled and couldn't understand why NUM didn't give me what I wanted. Num# is the key. Glad I found this.