I want to present the duration of a logged event from the alarm_events_data table and present the result in the format of HH24:MI:SS. We are using oracle as database.
I have no problem creating the query, the problem is to get it presented in the format I want for the operators
This is the select I am using (simplified)
SELECT
ae."displaypath",
ae."eventtime" as StartTime,
aeEnd."eventtime" as EndTime,
to_char(aeEnd."eventtime" - ae."eventtime" ,'HH24:Mi:SS') as Duration
FROM ALARM_EVENTS ae
left outer join ALARM_EVENTS aeEnd on ae."eventid" = aeEnd."eventid" and aeEnd."eventtype" = 1
where
ae."priority"='0'
and ae."eventtype" = 0
order by ae."eventtime" desc
Fetch first 1000 rows only
Start and end date is returned as expected and we are able to set the datetime format in the table where we display the result. But the calculated date is only showing in the format of "+000000000 00:25:27.30300" where want to display the result as "00:25:27"
I have tried without the "to_char" command but then I just get an error "Unknown SQL type"
If I try this directly at the oracle database the result is working as expected.
I have also tried to use the oracle command extract(time from aeEnd."eventtime" - ae."eventtime") this also return an error in igniton.