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