topic Re: Need to find difference in minutes using two timestamp fields in QlikView

Need to find difference in minutes using two timestamp fields

praveenkumar_s — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Mon, 26 Mar 2018 17:43:22 GMT

May be this

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

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Mon, 26 Mar 2018 18:38:53 GMT

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

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Mon, 26 Mar 2018 18:44:20 GMT

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?

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Mon, 26 Mar 2018 18:54:11 GMT

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.

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Mon, 26 Mar 2018 18:56:36 GMT

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')

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Mon, 26 Mar 2018 19:16:59 GMT

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.

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Mon, 26 Mar 2018 19:25:15 GMT

Try this

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

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Mon, 26 Mar 2018 19:52:21 GMT

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.

Re: Need to find difference in minutes using two timestamp fields

rwunderlich — Mon, 26 Mar 2018 20:58:14 GMT

Sunny, did you mean Ceil instead of floor?

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

Re: Need to find difference in minutes using two timestamp fields

Peter_Cammaert — Tue, 27 Mar 2018 12:07:51 GMT

I know it looks rather ugly, but AFAIK you can only consistently produce this outcome by doing exactly what you tell us to do: manupulate the seconds in the TimeStamp strings.

Create a Straight table with dimensions ORIG_RESTORE_TS and NEW_RESTORE_TS and the following expressions:

Strcat(ORIG): =Date#(Left(ORIG_RESTORE_TS, Index(ORIG_RESTORE_TS, ':', 2)) & '00' & Right(ORIG_RESTORE_TS, 3), 'MMM DD, YYYY hh:mm:ss TT')

Strcat(NEW): =IF (Len(NEW_RESTORE_TS) > 0, Date#(Left(NEW_RESTORE_TS, Index(NEW_RESTORE_TS, ':', 2)) & '00' & Right(NEW_RESTORE_TS, 3), 'MMM DD, YYYY hh:mm:ss TT'))

Diff: =Round(([Strcat(ORIG)] - [Strcat(NEW)])*24.0*60.0)

produces this:

No it doesn't look rather ugly, it looks extremely ugly. But it seems to work.

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Tue, 27 Mar 2018 12:11:47 GMT

Hey Rob - The reason I thought it would need Floor was because the OP wanted to convert 01:55 sec to 01 min , 02:25 sec to 02 min. Ceil will convert it to 02 and 03, respectively, right?

Re: Need to find difference in minutes using two timestamp fields

Peter_Cammaert — Tue, 27 Mar 2018 12:25:53 GMT

Indeed, and at the other end: Floor(-01:55) = -2 and Floor(02:25) = -3 too.

And you can't switch between Floor() and Ceil() in your expression, depending on whether TimeStamp2 (or ORIG_RESTORE_TS) Is GE or LT than TimeStamp1, because that breaks other rows. Tough one...

Re: Need to find difference in minutes using two timestamp fields

sasiparupudi1 — Tue, 27 Mar 2018 12:31:38 GMT

May be try like

Interval(

(TimeStamp(Timestamp#(ts1,'MMM DD,YYYY hh:mm:ss TT'),'MMM DD,YYYY hh:mm')

TimeStamp(Timestamp#(ts2,'MMM DD,YYYY hh:mm:ss TT'),'MMM DD,YYYY hh:mm'))

'mm')

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Tue, 27 Mar 2018 12:36:53 GMT

That is why I wasn't flooring the subtraction (well initially I did), but flooring the individual time stamps and then perform the subtraction

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Tue, 27 Mar 2018 14:42:16 GMT

hi peter,

I tried the method which you have done. but i didnt get any value in diff column(restore variation in my table) as see in the attachment below,

Re: Need to find difference in minutes using two timestamp fields

sunny_talwar — Tue, 27 Mar 2018 14:45:39 GMT

Can you share your qvw file here or data in an Excel file?

Re: Need to find difference in minutes using two timestamp fields

Peter_Cammaert — Tue, 27 Mar 2018 14:47:15 GMT

It looks like both the Strcat columns contain strings instead of duals. The expression in the Diff column tries to perform calculations on numerical values and those calculations won't work with strings. What expressions did you use in the Strcat columns?

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Tue, 27 Mar 2018 14:57:45 GMT

I have used the expression you gave. find below,

Strcat(ORIG):=Date#(Left(ORIG_RESTORE_TS, Index(ORIG_RESTORE_TS, ':', 2)) & '00' & Right(ORIG_RESTORE_TS, 3), 'MMM DD, YYYY hh:mm:ss TT')

Re: Need to find difference in minutes using two timestamp fields

praveenkumar_s — Tue, 27 Mar 2018 15:51:26 GMT

sunny,

Please find the QVW and sample source file