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