topic Re: timestamps difference in hours in QlikView

timestamps difference in hours

bvmike2001 — Mon, 20 Aug 2012 12:51:01 GMT

Hello,

how can i calculate two timestamps difference in hours form two timestamp fields F1 and F2?

Many thanks!

Re: timestamps difference in hours

swuehl — Mon, 20 Aug 2012 12:54:59 GMT

If your timestamps are correctly interpreted by QV, i.e. have a numerical representation, you should be able to just use:

=interval( F1 - F2, 'hh')

Re: timestamps difference in hours

bvmike2001 — Mon, 20 Aug 2012 13:03:10 GMT

How can i test if my timestamps fields are correctly interpreted by QV?

Re: timestamps difference in hours

swuehl — Mon, 20 Aug 2012 13:12:57 GMT

An indication is the correct label if you hover your mouse over the field in table view (CTRL-T). It should say timestamp, numerical (etc).

Then, by default the field values will be differently aligned (right) than pure text values (left).

If you use num(FIELD), you get the numerical representation returned, a number. If you use text(FIELD), you get the textual representation.

A QV timestamp is a dual and should have both representations (the numerical representation is the reason you can use numerical operators like +,- on timestamps or dates).

If your field is not correctly interpreted, use a correct standard format setting in your script

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

or use the QV functions timestamp#() resp. date#() to interpret your input values. Please check these functions and the date and time format codes.

There was also recently a nice blog entry by Henric Cronstroem explaining all this in detail.

Regards,

Stefan