topic Re: Convert Time stamp to as is number in QlikView

Convert Time stamp to as is number

MK9885 — Tue, 09 Mar 2021 17:48:39 GMT

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

Re: Convert Time stamp to as is number

rwunderlich — Tue, 09 Mar 2021 18:59:44 GMT

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

(end_time-start_time) * 1440

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

Re: Convert Time stamp to as is number

MK9885 — Tue, 09 Mar 2021 20:27:44 GMT

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

Re: Convert Time stamp to as is number

rwunderlich — Tue, 09 Mar 2021 23:09:00 GMT

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-QlikSense/ta-p/1484207

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

Re: Convert Time stamp to as is number

MK9885 — Thu, 18 Mar 2021 19:07:26 GMT

@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!

Re: Convert Time stamp to as is number

rwunderlich — Thu, 18 Mar 2021 21:26:46 GMT

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