Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
praveenkumar_s
Creator II
Creator II

Need to find difference in minutes using two timestamp fields

Hi friends,

I just need to find the difference between two timestamp columns by minutes and also i have to remove seconds . Any suggestion would be really helpful.

eg: jan 12,2017 11:27:28 PM and jan 12,2017 11:37:22 PM

Actual answer using interval function including seconds: 9 minutes

Expected answer : 10 minutes (remove seconds and have to find difference)

1 Solution

Accepted Solutions
sunny_talwar

Here is another approach

=Interval(TimeStamp#(TimeStamp(ORIG_RESTORE_TS, 'M/D/YYYY h:mm'), 'M/D/YYYY h:mm') - TimeStamp#(TimeStamp(NEW_RESTORE_TS, 'M/D/YYYY h:mm'), 'M/D/YYYY h:mm'), 'm')

View solution in original post

23 Replies
sunny_talwar

May be this

=Interval(Round(TimeStamp2 - TimeStamp1, 1/(24*60)), 'mm')

praveenkumar_s
Creator II
Creator II
Author

i have used your script, its working fine. but for some of the field it shows incorrect value as attached with highlighted by red box.

first red box it should show as -03

second red box it should show as 03

Screenshot_4.png

sunny_talwar

1st of all, are you rounding to the nearest minute or ceiling or flooring? 2nd, are you doing rounding/ceiling/flooring before subtraction or after?

praveenkumar_s
Creator II
Creator II
Author

Sunny,

i don't want to round off minutes. i want to remove seconds . eg-  01:55 sec to 01 min ,  02:25 sec to 02 min . like that i needed. consider, all the seconds value to 00. then it will come correctly.

sunny_talwar

That means you are flooring ....

You can try this

=Interval(Floor(TimeStamp2 - TimeStamp1, 1/(24*60)), 'mm')

or this

=Interval(Floor(TimeStamp2, 1/(24*60)) - Floor(TimeStamp1, 1/(24*60)), 'mm')

praveenkumar_s
Creator II
Creator II
Author

sorry. the expression you give will not give accurate result. if 1:00:00 and 1:03:38 will give -04 but i want -03.

sunny_talwar

Try this

=Interval(Floor(Round(TimeStamp2, 1/(24*60*2)), 1/(24*60)) - Floor(Round(TimeStamp1, 1/(24*60*2)), 1/(24*60)), 'mm')

praveenkumar_s
Creator II
Creator II
Author

thanks for your continous effort, but still its not comming

for jan 20,2017 8:16:52 and jan 20,2017 8:15:30 , it shows 2, but i want 1. instead of floor and ceil function i just want to replace seconds with '00' like jan 20,2017 8:16:52 as jan 20,2017 8:16:00 and jan 20,2017 8:15:30 as jan 20,2017 8:15:00 , then we can do the calculations.

rwunderlich

Sunny, did you mean Ceil instead of floor?

=Interval(Ceil(TimeStamp2 - TimeStamp1, 1/(24*60)), 'mm')