# How to get total alarm duration and occurrences by alarm

**URL:** <https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758>\
**Category:** General Discussion\
**Created:** [November 8, 2015, 8:03pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758 "2015-11-08T20:03:44Z")\
**Posts on this page:** 5\
**Page:** 2

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [August 15, 2023, 1:05am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/21 "2023-08-15T01:05:13Z")

</div>

> [@Lalillo1](#):
>
> ```auto
> cast(Sum(Date_Part('day', c.eventtime - a.eventtime) * 1440 
> + Date_Part('hour', c.eventtime - a.eventtime) * 60 
> + Date_Part('minute', c.eventtime - a.eventtime) + 
> Date_Part('second', c.eventtime - a.eventtime)*(1/60)) AS DECIMAL(10,2))
> 
> ```

This is overly complicated. You can just use:  
T-SQL

```auto
DATEDIFF(SECOND, c.eventtime, a.eventtime)/60.0;

```

Postgres:

```auto
EXTRACT(EPOCH FROM (c.eventtime - a.eventtime))/60.0 

```

---

<div class="post-metadata">

**Author:** ![Lalillo1](https://avatars.discourse-cdn.com/v4/letter/l/a6a055/32.png) [@Lalillo1](https://forum.inductiveautomation.com/u/Lalillo1)\
**Post date:** [August 15, 2023, 3:54am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/22 "2023-08-15T03:54:23Z")

</div>

> [@nminchin](#):
>
> `EXTRACT(EPOCH FROM (c.eventtime - a.eventtime))/60.0`

Thank you. Is this is what you were suggesting?

```auto
SELECT a.source, a.displaypath,
  cast(Sum(EXTRACT(EPOCH FROM (c.eventtime - a.eventtime))/60.0) AS DECIMAL(10,2)) AS
  duration,
  COUNT(*) AS total
FROM alarm_events a
  LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1
WHERE 
  a.eventtype = 0 and  
  a.source like 'prov:xxxxx:/tag:xxxxx/xxxx/xxxxx/Alarms/%' and
  a.eventtime >= '2023-08-10 09:30:20' AND a.eventtime <= '2023-08-14 12:30:20'
GROUP BY a.source, a.displaypath
ORDER BY duration DESC
LIMIT 10;

```

I just don't understand why still I have to divide by 60. Can you please help me understand?  
Oh never mind, I just read that following:  
"In Postgres, the EPOCH time represents the total number of seconds elapsed from “1st January 1970 00:00:00” till today"

> **[How to Calculate EPOCH Time in PostgreSQL](https://www.commandprompt.com/education/how-to-calculate-epoch-time-in-postgresql/)**
>
> In PostgreSQL, to extract epoch time from the current or specific time, the EXTRACT() function is used with the EPOCH argument.

---

<div class="post-metadata">

**Author:** ![NewBieNew](https://avatars.discourse-cdn.com/v4/letter/n/a183cd/32.png) [@NewBieNew](https://forum.inductiveautomation.com/u/NewBieNew)\
**Post date:** [January 2, 2024, 4:22pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/23 "2024-01-02T16:22:49Z")

</div>

Hi @vrautomation ,

I am trying to achieve the same with either label/name of the alarm. Any help is appreciated.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 2, 2024, 4:43pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/24 "2024-01-02T16:43:38Z")

</div>

> [@NewBieNew](#):
>
> I am trying to achieve the same with either label/name of the alarm.

You would need to tell us what you have done and where you are stuck. Be precise. If posting code then format it properly: [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831)

---

<div class="post-metadata">

**Author:** ![NewBieNew](https://avatars.discourse-cdn.com/v4/letter/n/a183cd/32.png) [@NewBieNew](https://forum.inductiveautomation.com/u/NewBieNew)\
**Post date:** [January 2, 2024, 4:50pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/25 "2024-01-02T16:50:38Z")

</div>

Thanks for your response.

> **[Ignition Exchange](https://inductiveautomation.com/exchange/57/versions)**
>
> Discover, share, and download community made Ignition resources in collaboration with your organization or the Ignition community.

I have imported an alarm analysis template from exchange.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/c/7cdf9a57338af946169a0efc02eefe152d6bb4b5.png)

For my pie charts it is looking for display path property of my alarms which has nothing in it hence showing 'Unknown'.

I want to get the alarm's Name/Label to be shown for the alarm frequency and duration.

This is for Ignition perspective 8.1.31

NOTE: Test something is me adding something to display path property for one of my alarms.

[Previous page](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758.md?page=1)