Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Timestamp and time difference in minutes and seconds

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!

1 Solution

Accepted Solutions

Re: Timestamp and time difference in minutes and seconds

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)

8 Replies

Re: Timestamp and time difference in minutes and seconds

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.

Not applicable

Re: Timestamp and time difference in minutes and seconds

Hey Bill,

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

Re: Timestamp and time difference in minutes and seconds

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

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

Not applicable

Re: Timestamp and time difference in minutes and seconds

I used an online converter

It shows

2014-07-07 15:24:29

2014-07-07 15:23:54

respectively

Not applicable

Re: Timestamp and time difference in minutes and seconds

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

Re: Timestamp and time difference in minutes and seconds

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)

Not applicable

Re: Timestamp and time difference in minutes and seconds

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

Community Browser