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: 
master_student
Creator III
Creator III

Intervall

Hello best community ever

I need to get the intervall between the two dates. The result would be in days/hours/minutes and seconds

What I am doing now :

Interval(date(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss') -date(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss'),'DD : hh : mm : ss')

but the result in days

Capture.PNG

I think the result in incorrect? isn't?

second question, I have 3900 second, I have to convert it in hours, any idea?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Marcus -

If this timestamps are already read as dual, do we need to use TimeStamp function before we use it with the interval? or did you wante to use TimeStamp#() function?

Interval(

    TimeStamp#(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss TT')

-    TimeStamp#(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss TT'),

'DD : hh : mm : ss')

View solution in original post

9 Replies
adamdavi3s
Master
Master

Seconds is pretty easy... 3900/60/60

the interval should work like that AFAIK

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

marcus_sommer

The result isn't correct because the 12-hour division of AM respectively PM aren't considered. Therefore try:

Interval(

     timestamp(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss TT')

-    timetsamp(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss TT'),

'DD : hh : mm : ss')

Maybe it worked without the timestamp like:

Interval(DATE_DERNIERE_MODIF - DATE_CREATION, 'DD : hh : mm : ss')

Your seconds could be converted into hours with:

round(3900 / (60 * 60), 0.01) = 1.08

- Marcus

adamdavi3s
Master
Master

Ah damn didn't spot the AM/PM issue

sunny_talwar

Marcus -

If this timestamps are already read as dual, do we need to use TimeStamp function before we use it with the interval? or did you wante to use TimeStamp#() function?

Interval(

    TimeStamp#(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss TT')

-    TimeStamp#(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss TT'),

'DD : hh : mm : ss')

marcus_sommer

Hi Sunny,

yes, you are right. If the values are dual the subtraction should work without any formatting and otherwise they should fail because strings couldn't be subtracted and would need a converting per timestamp#(). But it seems that it is different in this case if I look on the posted formula ...

- Marcus

master_student
Creator III
Creator III
Author

Fot the interval, the issue is resolved. but to convert seconds into hours, there are some wrong result especially if the seconds<3600, look :

Capture.PNG

the formula I am using is : =time(Time#(DUREE_AVANT_CHANGEMENT,'s'))

sunny_talwar

May be this:

=Interval(Time#(DUREE_AVANT_CHANGEMENT,'s'), 'h:mm:ss')

adamdavi3s
Master
Master

Your formula is giving you the number of seconds from midnight

What do you want it to show?

master_student
Creator III
Creator III
Author

Thanks a lot Sunny. Now I get the right results.