Search Postgresql Archives

Ideas for query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello List,

I am a not very experienced writing sql and I have a problem I can't readily solve, so
I was hoping to get some help from this great list.

Here is my problem I have a table that has event data about the status of units in the field. It has
many kinds of events one of which has down time information. I have written a query to extract that
information and calculate the % downtime. The problem I am having is that if the unit was never down
I don't see it in my query, I would like to be able to somehow report it as 100% up.

Below is an example of the event data and my query is below it. Any suggestions would be greatly appreciated.
t_unit has base information about the unit

v_unit_event_info
 group_id | unit_serial_no | event_category |                                           event_mesg                                            |          event_date          
----------+----------------+----------------+-------------------------------------------------------------------------------------------------+-------------------------------
 146      | K101334        | UNIT           | Unit is Up. Last packet received:2010-12-10 22:56:18.330648-05, Total down time:00:09:17.757409 | 2010-12-10 22:56:18.330648-05

select unit_serial_no,
sum(down_time),
round((100-(extract(epoch from sum(down_time))/extract(epoch from (timestamp '2011-09-31 23:59:59' - timestamp '2011-09-01 00:00:00')::interval))*100)::numeric,2) as "Up Time %"
from (select unit_serial_no, down_time
from (select unit_serial_no, substring(event_mesg from
strpos(event_mesg,'Total')+16 for 40)::interval as down_time
from v_unit_event_info where event_category='UNIT'
and event_mesg like '%Total %'
and event_date >= '2011-09-01 00:00:00'
and event_date <= '2011-09-30 23:59:59' and unit_serial_no in
(select unit_serial_no from t_unit where group_id='199' and activated='y')
order by unit_serial_no) as foo where down_time < '5 days')
as foo2 group by unit_serial_no;
--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@xxxxxxxxxxxxx
http://www.netwolves.com

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux