Convert Time stamp to as is number

[MK9885]

Hi,

I have following values

from_date 2020-01-01 12:00:00.000

to_date 2021-12-31 12:40:00.000

How do I get difference between these 2 date time stamp as 40

I can get difference but I want them in number as 40. Not 00:40

so I can sum the times as 40 + ...

Interval gives me difference but again in date timestamp format. not just the number 40.

I used SQL query
isnull(convert(datetime, from_date), '8:00') as start_time,
isnull(convert(datetime, to_date), '18:00') as end_time
Then in Qlik script I used

Time(Interval(end_time-start_time),'mm') as Total --- This gives me timestamp again. Not in Number

@sunny_talwar 

[rwunderlich]

Num#(Interval(end_time-start_time),'mm'))

or  

(end_time-start_time) * 1440

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[MK9885]

Tried, but they give me difference for date range times.

from_date 2020-01-01 12:00:00.000

to_date 2021-12-31 12:40:00.000

Date range above is from 01/01 to 12/31.. so it is giving diff of all the time between this range. But I just need 12:00 - 12:40.

I got it by using  Time(Interval end_time- start_time),'mm').
In list box it looks like number but when you export it to excel or sum the values, it is giving incorrect answer.
Ex: 40 & 24 is 65 but I get 05

[rwunderlich]

I recommend reading up on the difference between Formatting and Interpretation functions.  Here's a good article

https://community.qlik.com/t5/Qlik-Sense-Documents/Dealing-with-date-formats-in-QlikView-and-QlikSen...

I'll assume that your values are proper qlik timestamp values, that it, the number of days is the integer value and time is the fractional value.  If you want to ignore the date portion and just subtract the time portions use frac() to get the time (fractional) portion. 

Frac(end_time) - Frac(start_time)

If you want that in minutes multiply the result by 1440. 

-Rob

[MK9885 · Accepted solution]

@rwunderlich 

I used below expression in script to convert into mins with as is numbers.

First I extracted just the time  using 
Time(from_date) as From_TimeOnly,
Time(to_date) as To_TimeOnly,

Then to get only numbers
Interval#(To_TimeOnly, 'H:mm:ss TT') * 1440 - Interval#(From_TimeOnly, 'H:mm:ss TT') * 1440

Thanks for your help!

[rwunderlich]

Glad you got it to work.  Just to give you more to chew on.

Time(from_date) as From_TimeOnly,

is improper.  Time() does not extract the time. It formats the Datetime as a string time. That forces you to reinterpret the value with Interpret#() when you use the field.  Instead if, you did 

Time(Frac(from_date)) as From_TimeOnly,

then your expression could be:

(To_TimeOnly - From_TimeOnly) * 1440

This becomes important for example, if you try to use From_TimeOnly as a chart Dimension or sort the field. It would not work if you use just the Time() function.

-Rob