Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Substract of Date-Time Fields

Hi guys, how could I do the substract this fields (Resolved Time  -   Open Time)  and get Hours?

Resolved Time                                         Open Time

14/01/2010  10:28:45 a.m.                      04/01/2010  09:35:13 a.m.

thank you all guys!!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Well, if you're doing it at script time, you use the input format in the timestamp#() function, and the desired format in the timestamp() function, or as I generally prefer, in the TimestampFormat variable that you should see getting set at the top of your script.  Hmmm, looks like the timestamp#() function won't like your 'a.m.' either.  So maybe something like this:

SET TimestampFormat='DD/MM/YY hh:mm:ss';

LOAD *
,(RES_TIME - OP_TIME)*24 as "Hours to Resolve"
;
LOAD
timestamp(timestamp#(purgechar([Open Time],'.'))) as OP_TIME,
timestamp(timestamp#(purgechar([Resolved Time],'.'))) as RES_TIME,
...

View solution in original post

5 Replies
johnw
Champion III
Champion III

I assume these are QlikView timestamp fields already?  If so, subtract them and multiply by 24.  If not, use timestamp#() to convert them to timestamps, then subtract them and multiply by 24.

Not applicable
Author

Thanks Jhon, but is this fortmat that I should convert them to?

Timestamp#([Open Time],'hh:mm:ss') as OP_TIME,

Timestamp#([Resolved Time],'hh:mm:ss') as RES_TIME,

johnw
Champion III
Champion III

Well, if you're doing it at script time, you use the input format in the timestamp#() function, and the desired format in the timestamp() function, or as I generally prefer, in the TimestampFormat variable that you should see getting set at the top of your script.  Hmmm, looks like the timestamp#() function won't like your 'a.m.' either.  So maybe something like this:

SET TimestampFormat='DD/MM/YY hh:mm:ss';

LOAD *
,(RES_TIME - OP_TIME)*24 as "Hours to Resolve"
;
LOAD
timestamp(timestamp#(purgechar([Open Time],'.'))) as OP_TIME,
timestamp(timestamp#(purgechar([Resolved Time],'.'))) as RES_TIME,
...

Not applicable
Author

try with

=interval([Resolved Time]-[Open Time],'hh:mm')

Not applicable
Author

Thank you Jhon it works perfeclty!!