Hours between two dates

[niranjana]

Hi,

I have two dates in  different timestamp formats : one in 12-hr and another in 24 hr format. I need to calculate the number of hours between the two dates in  chart expression or dimension. 

For example : 

the dates are : 

19/11/2023 12:07:45

19/11/2023 11:05:29 AM

The result should be 1 hr. or(1:02:16 hrs)

Any idea how? Thanks in advance. I tried Interval() but it didnt work :

Interval((Time(Timestamp#(APPROVEDTIME ,'DD/MM/YYYY HH:MM:SS'),'HH:MM:SS')-

Timestamp(Time(MESSAGE_CREATED_DATE ,'DD/MM/YYYY HH:MM:SS tt'),'HH:MM:SS')

),'H'
)

[BernardBernoulli]

I'm still new to Qlik too, but I'd try to pre-set these timestamps in the Dataeditor like this:

Timestamp(Timestamp#(FIELD1, 'DD/MM/YYYY hh:mm:ss') ) AS [FIELD1],
Timestamp(Timestamp#(FIELD2, 'DD/MM/YYYY hh:mm:ss TT') ) AS [FIELD2],

 Maybe it helps

[steeefan]

Yes, indeed. For the duration then just simply subtract one from the other, which gives you the difference in days, and then mutiply by 24 for an hour value.

[niranjana]

 But one date (field 1) is in 12 hr format AM/PM...I changed in to 24 format but it changes the date as well. 

"Timestamp(Time(MESSAGE_CREATED_DATE ,'DD/MM/YYYY HH:MM:SS tt'),'HH:MM:SS')" I used this but it gives wrong dates after changing to 24 hr format.

Is there a way?

Niranjana

[rwunderlich · Accepted solution]

Your expression looks about right except the HH:MM:SS should be lower case hh:mm:ss.

-Rob

[Filipe]

Sou novo, mas estava procurando sobre datas... tente transformar ambas para número, realize a subtração e então transforme para o formato de data que desejar.

[niranjana]

Hi rob,

Thanks a lot for you reply. It works in chart but not in script. Do I have to modify the exp in script?

Niranjana

[niranjana]

Can you please type in English

[Ahidhar]

try this

ta:
load timestamp(Timestamp#(date,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as date,
timestamp(Timestamp#(date1,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss TT') as date1,
interval(timestamp(date)-timestamp(date1),'hh:mm:ss'),
interval(timestamp(date)-timestamp(date1),'hh');
load * Inline
[
date,date1
19/11/2023 12:07:45 PM,19/11/2023 11:05:29 AM
];

[Vegar]

To identify where your problem I suggest you to do this in steps.

First try to interpret the two fields individually as  timestamps in the script.  Once you've done that then you can subtract the two into an interval, and format that interval to 'hh:mm:ss' as you desired.