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'
)

[steeefan]

Combining what everybody in here said, this should definitely work for you:

Data:
NOCONCATENATE LOAD
  Interval(APPROVEDTIME-MESSAGE_CREATED_DATE, 'hh:mm:ss') AS DURATION,
  *;
LOAD
  Timestamp(Timestamp#(APPROVEDTIME, 'DD/MM/YYYY hh:mm:ss')) AS APPROVEDTIME,
  Timestamp(Timestamp#(MESSAGE_CREATED_DATE, 'DD/MM/YYYY hh:mm:ss TT')) AS MESSAGE_CREATED_DATE;
LOAD * INLINE [
  APPROVEDTIME, MESSAGE_CREATED_DATE
  19/11/2023 12:07:45, 19/11/2023 11:05:29 AM
];

[Gabbar]

Try Converting them into numbers and then multiplying it by 24 to get hours like:-
24*(num(APPROVEDTIME) - Num(MESSAGE_CREATED_DATE))  
But this will give you hours in decimal like 1.03 for current scenerion.

To get it into HH MM SS Format you can use timestamp like:-
Timestamp(num(APPROVEDTIME) - Num(MESSAGE_CREATED_DATE),'HH MM SS')

Make Sure your system identifies the Approved time and message_created_date column as Integer/Timestamp

[niranjana · Accepted solution]

Thanks it rob, small letters worked. I used this expression :

num#(Interval((Timestamp(Timestamp#(APPROVEDTIME,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')-
Timestamp(Timestamp(MESSAGE_CREATED_DATE ,'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss')),'h'))

Thanks

Niranjana