# Calculate duration of an event from Alarm\_Event\_Data when using Oracle as database

**URL:** <https://forum.inductiveautomation.com/t/calculate-duration-of-an-event-from-alarm-event-data-when-using-oracle-as-database/66998>\
**Category:** Ignition\
**Tags:** namedqueries\
**Created:** [November 28, 2022, 8:23pm UTC](https://forum.inductiveautomation.com/t/calculate-duration-of-an-event-from-alarm-event-data-when-using-oracle-as-database/66998 "2022-11-28T20:23:47Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Geir\_Doving](https://avatars.discourse-cdn.com/v4/letter/g/df788c/32.png) [@Geir\_Doving](https://forum.inductiveautomation.com/u/Geir_Doving)\
**Post date:** [November 28, 2022, 8:23pm UTC](https://forum.inductiveautomation.com/t/calculate-duration-of-an-event-from-alarm-event-data-when-using-oracle-as-database/66998/1 "2022-11-28T20:23:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 28, 2022, 8:42pm UTC](https://forum.inductiveautomation.com/t/calculate-duration-of-an-event-from-alarm-event-data-when-using-oracle-as-database/66998/2 "2022-11-28T20:42:21Z")

</div>

I would recommend returning the raw data from the query, and doing any formatting needed at the latest possible time.

What is the type of the column in the DB?