Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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)

View solution in original post

8 Replies
Anonymous
Not applicable
Author

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
Author

Hey Bill,

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

Anonymous
Not applicable
Author

Well that is not a a valid time.

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

its_anandrjs
Champion III
Champion III

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
Author

I used an online converter

It shows

2014-07-07 15:24:29

2014-07-07 15:23:54

respectively

Not applicable
Author

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

Anonymous
Not applicable
Author

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
Author

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