Timestamp and time difference in minutes and seconds

[#1]

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!

[Anonymous]

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.

[#3]

Hey Bill,

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

[Anonymous]

Well that is not a a valid time.

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

[its_anandrjs]

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

[#6]

I used an online converter

It shows

2014-07-07 15:24:29

2014-07-07 15:23:54

respectively

[#7]

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

[Anonymous · Accepted solution]

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)

[#9]

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