database query - logic question

[Israel Carr]

Thanks for anyone who takes the time to read this. If I posted to the
wrong list, I apologize and you can disregard.

I need help with a script to pull data from a postgres database. I'm ok
with the database connection just not sure how to parse the data to get
the results I need.

I'm running Python 2.4.4. For what it's worth, once I can get my logic
correct I'll be publishing the reports mentioned below via zope for web
clients.

Here is a small sample of the records in the table:

name date time status
machine1 01/01/2008 13:00:00 system ok
machine1 01/01/2008 13:05:00 system ok
machine1 01/01/2008 13:10:00 status1
machine1 01/01/2008 13:10:30 status1
machine1 01/01/2008 13:11:00 system ok
machine1 01/01/2008 13:16:30 status2
machine1 01/01/2008 13:17:00 status2
machine1 01/01/2008 13:17:30 status2
machine1 01/01/2008 13:18:00 status2
machine1 01/01/2008 13:18:30 status2
machine1 01/01/2008 13:19:00 system ok
machine1 01/01/2008 13:24:00 status2
machine1 01/01/2008 13:24:30 status2
machine1 01/01/2008 13:25:00 system ok

I need to report from this data.
The detail report needs to be something like:
machine1 01/01/2008 13:10:00 status1 00:01:30
machine1 01/01/2008 13:16:30 status2 00:02:30
machine1 01/01/2008 13:24:00 status2 00:01:00

and the summary needs to be
machine1 01/01/2008 total 'status1' time = 00:01:30
machine1 01/01/2008 total 'status2' time = 00:03:30
_____
machine1 01/01/2008 total 'non-OK' time = 00:05:00 #this is the
sum of status1 and status2 times

The 'machine1' system is periodically checked and the system status is
written to the database table with the machinename/date/time/status.
Everything that isn't a 'system ok' status is bad. For me to determine
the amount of time a machine was in a bad status I'm taking the first
time a machine has a 'system ok' status after a bad status and
subtracting from that time the time that a machine first went into that
bad status. From my table above:

machine1 went into 'status2' status at 13:16:30 and came out of
'status2' to a 'system ok' status at 13:19:00. So the downtime would be
13:19:00 - 13:16:30 = 00:02:30

I'm not sure how to query when a 'bad' status is found to find the next
'good' status and calculate based on the times. Essentially, I need
help creating the reports mentioned above. Your questions may also help
clarify my fuzzy description.

Thanks for any help. Reply with questions.

Israel

 

[Dennis Lee Bieber]

Here is a small sample of the records in the table:

name date time status
machine1 01/01/2008 13:00:00 system ok
machine1 01/01/2008 13:05:00 system ok
machine1 01/01/2008 13:10:00 status1
machine1 01/01/2008 13:10:30 status1
machine1 01/01/2008 13:11:00 system ok
machine1 01/01/2008 13:16:30 status2
machine1 01/01/2008 13:17:00 status2
machine1 01/01/2008 13:17:30 status2
machine1 01/01/2008 13:18:00 status2
machine1 01/01/2008 13:18:30 status2
machine1 01/01/2008 13:19:00 system ok
machine1 01/01/2008 13:24:00 status2
machine1 01/01/2008 13:24:30 status2
machine1 01/01/2008 13:25:00 system ok

I need to report from this data.
The detail report needs to be something like:
machine1 01/01/2008 13:10:00 status1 00:01:30
machine1 01/01/2008 13:16:30 status2 00:02:30
machine1 01/01/2008 13:24:00 status2 00:01:00

How are you calculating that final column?

13:11:00 - 13:10:00 => 00:01:00
13:11:00 - 13:05:00 => 00:06:00
13:10:30 - 13:05:00 => 00:04:30

I'm not sure how to query when a 'bad' status is found to find the next
'good' status and calculate based on the times. Essentially, I need
help creating the reports mentioned above. Your questions may also help
clarify my fuzzy description.

It sounds very much as if you are attempting to use the status field
as a control break for the report. Unfortunately, you aren't doing
something simple -- like adding up a value at each record into a total;
instead you want to take a value /at/ the control break, and difference
it with the value from the start of the control group. And if you are
only concerned with "machine1", you don't need to waste time returning
the name field...

I'd suggest just coding the control break handling in Python itself,
working directly from your first data sample. Though are you sure that
date and time were actually stored as separate fields? It's much easier
if they are a single datetime entity in the database AND get converted
to a datetime type by the DB-API being used.

-=-=-=-=- pseudo-code/untested

totals = {}
last_time = None
last_status = None

machine = "machine1"

#use the current placeholder for your db-api
cur.execute("""select datetime, status from some_table
where name = ?
order by datetime""",
(machine,) )

for (dt, st) in cur:
if st != last_status:
if last_status and last_time:
print "%30s %20s %10s %10s" % (machine,
last_time,
last_status
dt - last_time)
totals[last_status] = (totals.get(last_status, 0.0)
+ (dt - last_time) )
last_status = st
last_time = dt

#handle last record? has to be same status or above would trigger
if last_time and (dt != last_time):
print "%30s %20s %10s %10s" % (machine,
last_time,
last_status
dt - last_time)
totals[last_status] = (totals.get(last_status, 0.0)
+ (dt - last_time) )

#print summary
for st in totals:
print "..." % (machine, st, totals[st])

-=-=-=-=-=-=-

Now, after all this, if you think it is too slow, you might want to
look into coding the computation loop as a PostgreSQL (or are you really
using the ancient precursor Postgres?) stored procedure. I don't know
enough about stored procedures to know how to set up a multiple record
computed return (and the books I checked at work used examples that only
did a single record operation). May you have to create a temporary table
for the results, do the computation (replace the prints with an INSERT
INTO temptable...), and then end the stored procedure with a SELECT *
from temptable... and a DROP TABLE temptable.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/