Ignition Alarm History

alarm 数据库表字段说明

参考文档：https://docs.inductiveautomation.com/display/DOC81/Alarm+Journal
表名：alarm_event_data和alarm_events

报警等级

postgresql查询：

实时报警说明

当前活跃的报警，存在。
当前清除的、未确认的报警，存在。
当前清楚的、已确认的报警，不存在。

历史报警说明

当前活跃的报警，存在，产生一条记录。
当前清除的、未确认的报警，存在，产生一条记录。
当前清楚的、已确认的报警，存在，产生一条记录。

对应数据库：

postgresql 行列转换

– SELECT * FROM crosstab
– (
– ‘SELECT eventid, eventtype, eventtime FROM alarm_events ORDER BY 1,2’,
– ‘SELECT DISTINCT eventtype FROM alarm_events ORDER BY 1’
– )
– AS data_avg_temp_cross
– (eventid text, ActiveTime text, ClearTime text, AckTIme text)
– order by ActiveTime desc
– limit 10

– select name,
– sum(case when month=‘jan’ then avg_temp else 0 end) as jan,
– sum(case when month=‘oct’ then avg_temp else 0 end) as oct,
– sum(case when month=‘july’ then avg_temp else 0 end) as july,
– sum(case when month=‘apr’ then avg_temp else 0 end) as apr
– from data_avg_temp group by name
– – having name like ‘%1’ and length(name)=4 order by 年龄 desc

select eventid, string_agg(eventtype||‘:’||eventtime,‘,’)as eventtime_def, string_agg(eventtype||‘:’||eventflags,‘,’)as eventflagsdef from alarm_events
– where eventid =‘a90c4c30-3e4e-4ac5-a0c1-5105cba63778’
group by eventid