topic Re: Difference in minutes between two timestamps in App Development

Difference in minutes between two timestamps

mikegrattan — Sat, 16 Nov 2024 03:19:52 GMT

I have separate date and time fields loading to a Qlik Sense application.

The date fields are formatted in Qlik to look like: mm/dd/yyyy

The time fields are formatted in Qlik to look like: hh:mm:ss AM/PM.

I am concatenating the Date and Time fields and formatting them with the timestamp function.

The following expression seems to be giving me a working format:

timestamp#(Date(CALLDT) & ' ' & time(CALLTIME)) as CallInDateTime,

timestamp#(Date(NSDATE) & ' ' & time(NSTIME)) as NoShowDateTime

But when I try to subtract CallInDateTime from NoShowDateTime I get a result of -.20 and it should be 310 (minutes) when the CallInDateTime is 2/7/2020 4:00:00 PM and the NoShowDateTime is 2/7/2020 11:10:06 AM since there's five hours and ten minutes difference between those two timestamps.

Is there a better way to get a difference in minutes between two timestamps, or do I need to use a better expression to format my DateTime fields?

Thanks.

Re: Difference in minutes between two timestamps

mikegrattan — Fri, 07 Feb 2020 22:23:31 GMT

Update:

I found another topic answer that works great. Here's an expression based on the other topic answer:

IF(SchedDateTime >= NoShowDateTime,

Interval(SchedDateTime - NoShowDateTime,'hh:mm:ss'),TIME(Interval#('24:00:00','hh:mm:ss')-Interval(NoShowDateTime - SchedDateTime,'hh:mm:ss'),'hh:mm:ss')

)

Re: Difference in minutes between two timestamps

TimvB — Fri, 07 Feb 2020 22:29:53 GMT

You state that you subtracted CallInDateTime from NoShowDateTime (i.e., NoShowDateTime - CallInDateTime). In your example it would be: 2/7/2020 11:10:06 AM - 2/7/2020 4:00:00 PM which is a difference of -289.9 minutes, which is -0.20 day (289.9/60/24). This matches your result.

Instead, if you would like to have the result in minutes as you described, the following formula will work:

(CallInDateTime - NoShowDateTime) * 1440

Hope it helps!

Re: Difference in minutes between two timestamps

mikegrattan — Fri, 07 Feb 2020 23:04:06 GMT

Thanks for your contribution; it helps explain the result of my initial attempt. I ended up using the Interval expression as outlined above.

Thanks again!