topic Re: Timestamp and time difference in minutes and seconds in QlikView

Timestamp and time difference in minutes and seconds

Tue, 15 Jul 2014 16:25:37 GMT

Hey everyone,

I would like to calculate the time difference between 1404743069 and 1404743034 in minutes, my formula is =interval(1404743069 - 1404743034, 'mm:ss'), and I got the result 50400:00. How can it be? The real difference is just around 1 minute. Where went wrong?

Thanks a lot!

Re: Timestamp and time difference in minutes and seconds

Anonymous — Tue, 15 Jul 2014 16:33:53 GMT

For your times of 1404743069 & 1404743034 what time is that in hh:mm:ss format ?

You will then need to use the time#() function to convert them into decimal QlikView time formats.

Re: Timestamp and time difference in minutes and seconds

Tue, 15 Jul 2014 16:35:19 GMT

Hey Bill,

it showed 840:00:00 in hh:mm:ss format

Re: Timestamp and time difference in minutes and seconds

Anonymous — Tue, 15 Jul 2014 16:39:13 GMT

Well that is not a a valid time.

Do you know what time your numbers 1404743069 & 1404743034 correspond to ?

Re: Timestamp and time difference in minutes and seconds

its_anandrjs — Tue, 15 Jul 2014 16:41:45 GMT

Hi,

You have to convert this time into proper time format then you are able to use it, You can use Time# or Time function may be it is in Timestamp# then use Timestamp# or Timestamp.

Hope this helps

Re: Timestamp and time difference in minutes and seconds

Tue, 15 Jul 2014 16:42:55 GMT

I used an online converter

It shows

2014-07-07 15:24:29

2014-07-07 15:23:54

respectively

Re: Timestamp and time difference in minutes and seconds

Tue, 15 Jul 2014 16:46:04 GMT

They are in timestamp, but how could I use Timestamp# in this example?

Re: Timestamp and time difference in minutes and seconds

Anonymous — Tue, 15 Jul 2014 16:53:24 GMT

Looks like you have got a Unix style timestamp, try converting it with :

Timestamp( Timestamp('1970-01-01 00:00:00.000') + [YourUnixTimeStamp] /60/60/24)

or to hard code you unix timestamp into the expression.

Timestamp( Timestamp('1970-01-01 00:00:00.000') + 1404743069 /60/60/24)

Re: Timestamp and time difference in minutes and seconds

Tue, 15 Jul 2014 16:59:09 GMT

I also realized perhaps the timestamp in qlikview is different from unix (sorry, I am new to Qlikview).

Finally it works!

Thank you so much, Bill