Interval hours

[firoz24]

 Hi all,

I am trying to get the interval hours between my field and current timestamp 

Below I have attached the script :-

Interval(Now(1) - Timestamp#([SYSDATE], 'YYYY-MM-DD hh:mm:ss [fff] TT'), 'hh') as Difference_In_Hours,

Interval(Now(1) - Timestamp(Timestamp#(SYSDATE, 'YYYY-MM-DD hh:mm:ss TT')), 'hh') as Difference_In_Hours1,

SYSDATE  Field format  - 2024-06-04 09:23:58. AM 

If i have get the interval hours I will use it in set analysis 

For eg:- need to count only the VIN (field) which is [Difference in Hours] greater than 16 hours 

[tresesco]

Looking at the sample value, your format has to be corrected, like:
'YYYY-MM-DD hh:mm:ss.[fff] TT'

Note : blank space, dot - all matter

[firoz24]

Hi Tresesco Actually in my sql db the SYSDATE Field Format - 2020-11-07 16:52:00.000

So my set format in qlik - SET TimeFormat='hh:mm:ss.fff';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss.[fff]';

So check my changed script again:--

Now(1) as Current_timestamp,

0-Ceil(Interval(Now(1) - Timestamp#([SYSDATE], 'YYYY-MM-DD hh:mm:ss'),'hh')) as Difference_In_Hours,

Interval(Now(1) - Timestamp(Timestamp#(SYSDATE, 'YYYY-MM-DD hh:mm:ss')),'hh') as Difference_In_Hours1,

Please check what is the issue 

I have attached the image too

[Vegar]

It looks like both fields are recognized ad timestamps. There should be no need to use the timestamp#() function on them.

Try without using timestamp#()

[firoz24]

So you mean I remove the timestamp ?

Interval(Now(1) - Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss')),'hh' as Difference_In_Hours1,

But i am getting data model like this

[tresesco]

Field values are right-aligned in the listbox, that means they are being treated as numeric. Hence, you can get rid of parsing function, and try like:
Interval(Now(1) - SYSDATE,'hh') as Difference_In_Hours1

[firoz24]

Hi 

I am getting like this now 

Interval(Now(1) - SYSDATE,'hh') as Difference_In_Hours1,

if ([Difference_In_Hours1] >= 08 ,'1','0') as Final_Difference_hours,

I hope this is correct but still in set analysis the KPI Vallue i am getting is wrong

Please check 

[tresesco]

If you want to compare with 8 hours, then you have to write like:

if ([Difference_In_Hours1] >= 8/24 ,1,0) as Final_Difference_hours

Because, 1 hour = 1/24 (in number)

Also, in your script, better use 'h' instead of 'hh' to avoid additional 0 preceding 

[firoz24 · Accepted solution]

Solved my issue,

Thank you Tresesco and vegar