How to get total alarm duration and occurrences by alarm

General Discussion
21

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

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

Postgres:

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

Thank you. Is this is what you were suggesting?

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"

23

Hi @vrautomation ,

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

24

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

25

Thanks for your response.

I have imported an alarm analysis template from exchange.

image
image1904×801 53.7 KB

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.